MySQL :: MySQL 8.4 参考手册 :: 26.2.7 MySQL 分区如何处理 NULL

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / MySQL 分区如何处理 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)

可以使用以下针对 INFORMATION_SCHEMA 数据库中的 PARTITIONS 表的查询查看这两个 CREATE TABLE 语句创建的分区

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 语句”。）

对于使用 SQL 函数的分区表达式，NULL 也以这种方式处理。假设我们使用类似下面的 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 一样。我们可以创建包含 NULL 的值列表的表 ts2 和 ts3，如下所示

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) 等等也是有效的。可以将列 c1 为 NULL 的行插入到表 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 来验证哪些分区用于存储行。

使用 HASH 和 KEY 分区处理 NULL. 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。

上一个首页向上下一个