26.2.7 How MySQL Partitioning Handles NULL — MySQL 8.4

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.21, «Таблица 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 для этого типа разбиения.