MySQL :: MySQL 8.3 Reference Manual :: 26.2.7 How MySQL Partitioning Handles NULL

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / How MySQL Partitioning Handles NULL

26.2.7 MySQL 分区处理 NULL

MySQL 中的分区不禁止将 NULL 作为分区表达式的值，无论它是一个列值还是用户提供的表达式的值。尽管允许使用 NULL 作为必须产生整数的表达式的值，但需要记住 NULL 不是一个数字。MySQL 的分区实现将 NULL 视为小于任何非 NULL 值，就像 ORDER BY 一样。

这意味着 NULL 的处理方式在不同的分区类型之间有所不同，并可能产生您不期望的行为。因此，在本节中，我们将讨论每种 MySQL 分区类型如何处理 NULL 值以确定行应存储在哪个分区中，并提供每种类型的示例。

RANGE 分区中的 NULL 处理 如果您将一行插入到使用 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)

您可以通过重新运行前一个查询来查看哪些分区用于存储插入的行，并检查输出：

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 中。

LIST 分区中的 NULL 处理 通过 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视为任何其他值。例如，both VALUES IN (NULL) 和 VALUES IN (1, 4, 7, NULL) 都是有效的，如同 VALUES IN (1, NULL, 4, 7)、VALUES IN (NULL, 1, 4, 7) 等。您可以将一行插入到表 ts2 和 ts3 中，其中 c1 列的值为 NULL：

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 分区的表并将记录插入其中来验证这种行为。假设您在 p 数据库中创建了一个表 th，使用以下语句：

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 对于这种类型的分区。

PREV HOME UP NEXT