19.2.7 How MySQL Partitioning Handles NULL

19.2.7 How MySQL Partitioning Handles NULL

在MySQL中,分区没有不允许NULL 作为分区表达式的值,不管它是列值或者用户提供的表达式的值。尽管,它被允许使用NULL 作为

一个表达式的值,否则必须是一个整数,重要的是要记住 NULL不是一个数字.MySQL 分区对于NULL 作为 less than any non-NULL 值

就像ORDER BY 那样。

这意味着不同类型的分区 对待空值,可能产生的行为,你不期望 你不准备为它。

在这种情况下,每个MySQL 分区类型 处理NULL值,当确定值应该存储在哪个分区,提供下面的例子:

用RANGE 分区处理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 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)

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)
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)

| t1 | p0 | 1 | 20 | 20 |

| t2 | p0 | 1 | 20 | 20 |

你还可以证明,那些记录被存储在最小的分区通过删除那些分区,然后重新运行查询语句:

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)

NULL 也可以用这种方式处理用于分区表达式 ,假设我们用一个创建表语句定义了一个表，例如：

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 被处理通过分区表达式对待为 a value less than any other value, and so is inserted into partition p0.

在LIST 列表中处理NULL, 一个LIST 分区的表允许NULL值,如果它其中的一个分区被定义使用vlaue-list 包含NULL,

相反的是 一个LIST 分区 不显示的使用NULL 作为list值 会拒绝,使用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含有空值的列表，如下所示:

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) and VALUES IN (1, 4, 7, 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)