26.2.7 How MySQL Partitioning Handles NULL — MySQL 9.2

26.2.7 Как MySQL Partitioning Обрабатывает NULL

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

Это означает, что обработка NULL различается в зависимости от типа разбиения и может привести к поведению, которого вы не ожидаете, если не подготовитесь к этому. В связи с этим, в этом разделе мы обсудим, как каждый тип разбиения MySQL обрабатывает значения NULL при определении раздела, в котором должна храниться строка, и приведем примеры для каждого.

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

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 t2 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY RANGE(c1) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (10),
    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

Вы можете увидеть разделы, созданные этими двумя CREATE TABLE операторами, используя следующий запрос к таблице PARTITIONS в базе данных INFORMATION_SCHEMA:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |           0 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          0 |              0 |           0 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

(Дополнительную информацию об этой таблице см. в разделе 28.3.22, «Таблица INFORMATION_SCHEMA PARTITIONS».) Теперь давайте заполним каждую из этих таблиц одной строкой, содержащей NULL в столбце, используемом в качестве ключа разбиения, и проверим, что строки были вставлены с помощью пары SELECT операторов:

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)

Вы можете увидеть, какие разделы используются для хранения вставленных строк, повторно выполнив предыдущий запрос к INFORMATION_SCHEMA.PARTITIONS и просмотрев вывод:

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |             20 |          20 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          1 |             20 |          20 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

Вы также можете продемонстрировать, что эти строки были сохранены в разделе с наименьшим номером каждой таблицы, удалив эти разделы, а затем повторно выполнив 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)

(Дополнительную информацию о ALTER TABLE ... DROP PARTITION см. в разделе 15.1.9, «Оператор ALTER TABLE».)

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

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. Строка со значением столбца dt, равным NULL, обрабатывается так, как будто выражение разбиения вычислялось до значения, меньшего, чем любое другое значение, и поэтому вставляется в раздел p0.

Обработка NULL с разбиением LIST. Таблица, которая разбита по принципу 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

В ts1 можно вставлять только строки, имеющие значение c1 в диапазоне от 0 до 8 включительно. 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)

Выполнив соответствующий запрос к INFORMATION_SCHEMA.PARTITIONS, вы можете определить, какие разделы были использованы для хранения только что вставленных строк (мы предполагаем, как и в предыдущих примерах, что таблицы с разбиением были созданы в базе данных p):

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2        | p0             |          0 |              0 |           0 |
| ts2        | p1             |          0 |              0 |           0 |
| ts2        | p2             |          0 |              0 |           0 |
| ts2        | p3             |          1 |             20 |          20 |
| ts3        | p0             |          0 |              0 |           0 |
| ts3        | p1             |          1 |             20 |          20 |
| ts3        | p2             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

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

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

mysql> CREATE TABLE th (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

Разделы этой таблицы можно просмотреть с помощью показанного здесь запроса:

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |           0 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

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

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

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

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          2 |             20 |          20 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

Повторив последний пример, используя PARTITION BY KEY вместо PARTITION BY HASH в определении таблицы, можно проверить, что NULL также обрабатывается как 0 для этого типа разбиения.