Как выделитель разделов в mysql обрабатывает значения NULL

 

Выделение разделов в MySQL не делает ничего, чтобы отвергнуть NULL как значение выражения выделения разделов независимо от того, является ли это значением столбца или обеспеченного пользователем выражения. Даже разрешается использовать NULL как значение выражения, которое должно выдать целое число, но важно иметь в виду, что NULL числом не является. Начиная с версии 5.1.8, MySQL обрабатывает NULL как будто он меньше, чем любое не нулевое значение, точно как делает ORDER BY.

Из‑за этого эта обработка NULL изменяется при выделении разделов различных типов, и может производить поведение, которое Вы не ожидаете. Если Вы вставляете строку в таблицу, разбитую на разделы RANGE так, что значение столбца, используемое, чтобы определить раздел, является NULL, строка вставлена в самый нижний раздел. Например, рассмотрите эти две таблицы, созданные и заполняемые следующим образом:

 

mysql> CREATE TABLE t1 (c1 INT, c2 VARCHAR(20))

– > PARTITION BY RANGE(c1) (

– > PARTITION p0 VALUES LESS THAN (0),

– > PARTITION p1 VALUES LESS THAN (10),

– > PARTITION p2 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.09 sec)

 

mysql> CREATE TABLE t1 (c1 INT, c2 VARCHAR(20))

– > PARTITION BY RANGE(c1) (

– > PARTITION p0 VALUES LESS THAN (‑5),

– > PARTITION p1 VALUES LESS THAN (0),

– > PARTITION p1 VALUES LESS THAN (10),

– > PARTITION p2 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.09 sec)

 

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');

Query OK, 1 row affected (0.00 sec)

 

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM t1;

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| id | name |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| NULL | mothra |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM t2;

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| id | name |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

| NULL | mothra |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑+

1 row in set (0.00 sec)

 

Вы можете видеть, в который раздел строки сохранены, осматривая файловую систему и сравнивая размеры.MYD‑файлов:

/var/lib/mysql/test> ls ‑l *.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 20 2006‑03‑10 03:27 t1#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 03:17 t1#P#p1.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 03:17 t1#P#p2.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 20 2006‑03‑10 03:27 t2#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 03:17 t2#P#p1.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 03:17 t2#P#p2.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 03:17 t2#P#p3.MYD

 

Файлы разделов именованы согласно формату table_name #P# partition_name. extension так, чтобы t1#P#p0.MYD был файлом, в котором сохранены данные, принадлежащие к разделу p0 таблицы t1. Обратите внимание: до MySQL 5.1.5, эти файлы были бы именованы соответственно t1_p0.MYD и t2_p0.MYD.

Вы можете также показывать, что эти строки были сохранены в самом низком разделе каждой таблицы, удаляя эти разделы, а затем делая повторный запуск инструкций SELECT:

 

mysql> ALTER TABLE t1 DROP PARTITION p0;

Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t2 DROP PARTITION p0;

Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM t1;

Empty set (0.00 sec)

mysql> SELECT * FROM t2;

Empty set (0.00 sec)

 

Такая обработка также верна для выделения разделов с помощью выражений через использование функций SQL. Предположите, что мы имеем таблицу:

 

CREATE TABLE tndate (id INT, dt DATE) PARTITION BY RANGE(YEAR(dt)) (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE);

 

Как с другими функциями MySQL, YEAR(NULL) вернет NULL. Строка со значением NULL столбца dt обрабатывается, как если бы выражение выделения разделов было оценено к значению меньше, чем любое другое значение, и так вставлено в раздел p0.

Таблица, которая разбита на разделы LIST допускает значения NULL если (и только если!) один из разделов определен, используя список значений, который содержит NULL. Таблица, разбитая на разделы LIST, которая явно не использует NULL в списке значений, отклоняет строки, приводящие к значению NULL для выражения выделения разделов, как показано в этом примере:

 

mysql> CREATE TABLE ts1 (c1 INT, c2 VARCHAR(20))

– > PARTITION BY LIST(c1) (

– > PARTITION p0 VALUES IN (0, 3, 6),

– > PARTITION p1 VALUES IN (1, 4, 7),

– > PARTITION p2 VALUES IN (2, 5, 8));

Query OK, 0 rows affected (0.01 sec)

 

mysql> INSERT INTO ts1 VALUES (9, 'mothra');

ERROR 1504 (HY000): Table has no partition for value 9

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');

ERROR 1504 (HY000): Table has no partition for value NULL

 

Только строки, имеющие значение c1 между 0 и 8 включительно, могут быть вставлены в ts1. NULL выходит за пределы этого диапазона точно так же, как число 9. Мы можем создавать таблицы ts2 и ts3 и списки значений, содержащие NULL, как показано здесь:

 

mysql> CREATE TABLE ts2 (c1 INT, c2 VARCHAR(20))

– > PARTITION BY LIST(c1) (

– > PARTITION p0 VALUES IN (0, 3, 6),

– > PARTITION p1 VALUES IN (1, 4, 7),

– > PARTITION p2 VALUES IN (2, 5, 8),

– > PARTITION p3 VALUES IN (NULL));

Query OK, 0 rows affected (0.01 sec)

 

mysql> CREATE TABLE ts3 (c1 INT, c2 VARCHAR(20))

– > PARTITION BY LIST(c1) (

– > PARTITION p0 VALUES IN (0, 3, 6),

– > PARTITION p1 VALUES IN (1, 4, 7, NULL),

– > PARTITION p2 VALUES IN (2, 5, 8));

Query OK, 0 rows affected (0.01 sec)

 

При определении значения для выделения разделов, Вы можете обрабатывать NULL точно как любое другое значение, и допустимы VALUES IN (NULL) и VALUES IN (1, 4, 7, NULL) (равно как и VALUES IN (1, NULL, 4, 7), VALUES IN (NULL, 1, 4, 7) и тому подобное). Вы можете вставлять строку, имеющую NULL для столбца c1 в каждую из таблиц ts2 и ts3:

 

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');

Query OK, 1 row affected (0.00 sec)

 

Осматривая файловую систему, Вы можете проверить, что первая из этих инструкций вставила новую строку в раздел p3 таблицы ts2, а вторая инструкция вставила новую строку в раздел p1 таблицы ts3:

 

/var/lib/mysql/test> ls ‑l ts2*.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 10:35 ts2#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 10:35 ts2#P#p1.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 10:35 ts2#P#p2.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 20 2006‑03‑10 10:35 ts2#P#p3.MYD

 

/var/lib/mysql/test> ls ‑l ts3*.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 10:36 ts3#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 20 2006‑03‑10 10:36 ts3#P#p1.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2006‑03‑10 10:36 ts3#P#p2.MYD

 

Как в более ранних примерах, мы принимаем использование оболочки bash в операционной системе для Unix для списка файлов. Например, если Вы используете оболочку DOS в операционной системе Windows эквивалент последнего вывода мог быть получен, выполняя команду dir ts3*.MYD в каталоге C:\Program Files\MySQL\MySQL Server 5.1\data\test.

Как показано ранее в этом разделе, Вы можете также проверять, которые разделы использовались для сохранения значений удаляя их, а затем выполняя SELECT.

NULL обработан несколько по‑другому для таблиц, разбитых на разделы HASH или KEY. В этих случаях любое выражение раздела, которое выдает значение NULL, обрабатывается, как если бы возвращаемое значение было нулевым. Мы можем проверять это поведение, исследуя эффекты в файловой системе от создания таблицы, разбитой на разделы HASH и начальной загрузкой с записью, содержащей соответствующие значения. Предположите, что Вы имеете таблицу th, созданную в базе данных test, используя эту инструкцию:

 

mysql> CREATE TABLE th (c1 INT, c2 VARCHAR(20))

– > PARTITION BY HASH(c1) PARTITIONS 2;

Query OK, 0 rows affected (0.00 sec)

 

При принятии установки RPM MySQL под Linux, эта инструкция создает два.MYD‑файла в /var/lib/mysql/test, которые могут просматриваться в оболочке bash следующим образом:

 

/var/lib/mysql/test> ls th*.MYD ‑l

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2005‑11‑04 18:41 th#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2005‑11‑04 18:41 th#P#p1.MYD

 

Обратите внимание, что размер каждого файла 0 байтов. Теперь вставьте в th строку, чей столбец c1 является NULL, и проверьте, что эта строка была вставлена:

 

mysql> INSERT INTO th VALUES (NULL, 'mothra');

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM th;

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

| c1 | c2 |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

| NULL | mothra |

+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+

1 row in set (0.01 sec)

 

Заметьте, что для любого целого числа N значение NULL MOD N всегда NULL. Для таблиц, которые разбиты на разделы HASH илм KEY, этот результат обрабатывается для определения правильного раздела как 0. При возврате к оболочке системы, мы можем видеть, что значение было вставлено в первый раздел (по умолчанию p0), выводя файлы данных еще раз:

 

var/lib/mysql/test> ls *.MYD ‑l

– rw‑rw‑‑‑‑ 1 mysql mysql 20 2005‑11‑04 18:44 th#P#p0.MYD

– rw‑rw‑‑‑‑ 1 mysql mysql 0 2005‑11‑04 18:41 th#P#p1.MYD

 

Вы можете видеть, что инструкция INSERT изменила только файл th#P#p0.MYD (увеличение размера на диске) без того, чтобы воздействовать на другой файл данных.

Важно: до MySQL 5.1.8 выделение разделов по RANGE значение выражения выделения разделов NULL работало как 0 (единственный способ обходить это должен был разработать таблицы так, чтобы не позволить пустые указатели, обычно объявляя столбцы NOT NULL). Если Вы имеете схему выделения разделов RANGE, которая зависит от этого более раннего поведения, Вы будете должны заново выполнить это при обновлении до MySQL 5.1.8 или позже.

 

Управление разделами

 

MySQL 5.1 обеспечивает ряд способов изменить разбитые на разделы таблицы. Возможно добавлять, удалять, переопределять, объединять или расчленять существующие разделы. Все эти действия могут быть выполнены, используя расширения выделения разделов к команде ALTER TABLE. Имеются также способы получить информацию относительно разбитых на разделы таблиц и разделов.

Обратите внимание: в MySQL 5.1 все разделы разбитой на разделы таблицы должны иметь то же самое число подразделов, и невозможно изменить подвыделение разделов, если только таблица была создана.

Инструкция ALTER TABLE … PARTITION BY … доступна с MySQL 5.1.6, предварительно, в MySQL 5.1, это было принято как допустимый синтаксис, но инструкция не делала ничего.

Чтобы изменять схему выделения разделов таблицы, необходимо использовать команду ALTER TABLE с предложением partition_options. Это предложение имеет тот же самый синтаксис, как то, что используется с CREATE TABLE для создания разбитой на разделы таблицы, и всегда начинается с ключевых слов PARTITION BY. Например, предположите, что Вы имеете таблицу, разбитую на разделы диапазоном, использующим следующую инструкцию CREATE TABLE:

 

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)

PARTITION BY RANGE(YEAR(purchased)) (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (1995),

PARTITION p2 VALUES LESS THAN (2000),

PARTITION p3 VALUES LESS THAN (2005));

 

Чтобы заново выделять разделы этой таблицы так, чтобы это было разбито на разделы ключом на два раздела, использующие значение столбца id как основание для ключа, Вы можете использовать эту инструкцию:

 

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

 

Это имеет тот же самый эффект на структуре таблицы как удаление таблицы и создания ее вновь, используя CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;.

Важно: в MySQL 5.1.7 и ранее ALTER TABLE … ENGINE = … удаляет все выделение разделов из обрабатываемой таблицы. Начиная с MySQL 5.1.8, этот оператор меняет только тип памяти, используемый таблицей, и оставляет схему выделения разделов таблицы неповрежденной. С MySQL 5.1.8 применяйте ALTER TABLE … REMOVE PARTITIONING, чтобы удалить выделение разделов таблицы.