MySQL分区表对NULL值的处理

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  
• 作者：王权富贵
  

1.概述

MySQL的分区表没有禁止NULL值作为分区表达式的值，无论它是列值还是用户提供的表达式的值，需要记住NULL值不是数字。MySQL的分区实现中将NULL视为小于任何非NULL值，与order by类似。
2.range分区表处理NULL

1.创建range分区表

1. CREATE TABLE t_range (
2. c1 INT,
3. c2 VARCHAR(20)
4. )
5. PARTITION BY RANGE(c1) (
6.   PARTITION p0 VALUES LESS THAN (0),
7.   PARTITION p1 VALUES LESS THAN (10),
8.   PARTITION p2 VALUES LESS THAN MAXVALUE
9. );

复制代码

2.插入2条分区列为null值的数据

1. insert into t_range values (NULL,'a'),(NULL,'b');

复制代码

3.查看数据的分布情况

1. mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2. FROM INFORMATION_SCHEMA.PARTITIONS
3. WHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME = 't_range';
4. +------------+----------------+------------+----------------+-------------+
5. | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6. +------------+----------------+------------+----------------+-------------+
7. | t_range    | p0             |          2 |           8192 |       16384 |
8. | t_range    | p1             |          0 |              0 |       16384 |
9. | t_range    | p2             |          0 |              0 |       16384 |
10. +------------+----------------+------------+----------------+-------------+
11. 3 rows in set (0.01 sec)
13. mysql> select * from t1 partition(p0);
14. +------+------+
15. | c1   | c2   |
16. +------+------+
17. | NULL | a    |
18. | NULL | b    |
19. +------+------+
20. 2 rows in set (0.00 sec)

复制代码

可以看到分区列包含null值的2条数据都分布在p0分区上。
3.list分区表处理NULL

1.创建2张list分区表，t_list1分区列包含null值，t_list2分区列中不包含null值

1. CREATE TABLE t_list1 (
2. c1 INT,
3. c2 VARCHAR(20)
4. )
5. PARTITION BY LIST(c1) (
6.     PARTITION p0 VALUES IN (0, 3, 6),
7.     PARTITION p1 VALUES IN (1, 4, 7),
8.     PARTITION p2 VALUES IN (2, 5, 8),
9.     PARTITION p3 VALUES IN (NULL)
10. );
12. CREATE TABLE t_list2 (
13. c1 INT,
14. c2 VARCHAR(20)
15. )
16. PARTITION BY LIST(c1) (
17.     PARTITION p0 VALUES IN (0, 3, 6),
18.     PARTITION p1 VALUES IN (1, 4, 7),
19.     PARTITION p2 VALUES IN (2, 5, 8)
20. );

复制代码

2.分别向2张表中插入2条分区列为null值的数据

1. mysql> insert into t_list1 values (NULL,'a'),(NULL,'b');
2. Query OK, 2 rows affected (0.01 sec)
3. Records: 2  Duplicates: 0  Warnings: 0
5. mysql> insert into t_list2 values (NULL,'a'),(NULL,'b');
6. ERROR 1526 (HY000): Table has no partition for value NULL

复制代码

可以看到 t_list2 表的分区列中不包含null值，所以数据插入失败。
3.查看数据的分布情况

1. mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
2. FROM INFORMATION_SCHEMA.PARTITIONS
3. WHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME = 't_list1';
4. +------------+----------------+------------+----------------+-------------+
5. | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
6. +------------+----------------+------------+----------------+-------------+
7. | t_list1    | p0             |          0 |              0 |       16384 |
8. | t_list1    | p1             |          0 |              0 |       16384 |
9. | t_list1    | p2             |          0 |              0 |       16384 |
10. | t_list1    | p3             |          2 |           8192 |       16384 |
11. +------------+----------------+------------+----------------+-------------+
12. 4 rows in set (0.00 sec)

复制代码

可以看到 t_list1 表中插入的2条包含null值的数据，由于p3分区包含null值列，所以2条数据分布在p3分区中。
4.hash/key分区表处理NULL

1.创建2张测试表，一张hash分区表，一张key分区表

1. CREATE TABLE t_hash (
2. c1 INT,
3. c2 VARCHAR(20)
4. )
5. PARTITION BY HASH(c1)
6. PARTITIONS 2;
8. CREATE TABLE t_key (
9. c1 INT,
10. c2 VARCHAR(20)
11. )
12. PARTITION BY key(c1)
13. PARTITIONS 2;

复制代码

2.分别向2张表中插入3条分区列为null值的数据

1. mysql> insert into t_hash values (NULL,'a'),(0,'b'),(1,'c');
2. Query OK, 3 rows affected (0.00 sec)
3. Records: 3  Duplicates: 0  Warnings: 0
5. mysql> insert into t_key values (NULL,'a'),(0,'b'),(1,'c');
6. Query OK, 3 rows affected (0.01 sec)
7. Records: 3  Duplicates: 0  Warnings: 0

复制代码

3.查看数据的分布情况

1. mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME in ('t_hash','t_key');
2. +------------+----------------+------------+----------------+-------------+
3. | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
4. +------------+----------------+------------+----------------+-------------+
5. | t_hash     | p0             |          2 |           8192 |       16384 |
6. | t_hash     | p1             |          1 |          16384 |       16384 |
7. | t_key      | p0             |          2 |           8192 |       16384 |
8. | t_key      | p1             |          1 |          16384 |       16384 |
9. +------------+----------------+------------+----------------+-------------+
10. 4 rows in set (0.00 sec)
12. mysql> select * from t_hash partition(p0);
13. +------+------+
14. | c1   | c2   |
15. +------+------+
16. | NULL | a    |
17. |    0 | b    |
18. +------+------+
19. 2 rows in set (0.00 sec)
21. mysql> select * from t_key partition(p0);
22. +------+------+
23. | c1   | c2   |
24. +------+------+
25. | NULL | a    |
26. |    1 | c    |
27. +------+------+
28. 2 rows in set (0.00 sec)

复制代码

可以看到分区列中包含null值的记录都在p0分区。
4.如果我们增加hash/key分区表的分区数，分区列为null值的记录会分布到其他分区

1. # 创建hash/key分区表，分区数为3
2. CREATE TABLE t_hash1 (
3. c1 INT,
4. c2 VARCHAR(20)
5. )
6. PARTITION BY HASH(c1)
7. PARTITIONS 3;
9. CREATE TABLE t_key1 (
10. c1 INT,
11. c2 VARCHAR(20)
12. )
13. PARTITION BY key(c1)
14. PARTITIONS 3;
17. # 插入数据
18. insert into t_hash1 values (NULL,'a'),(0,'b'),(1,'c');
19. insert into t_key1 values (NULL,'a'),(0,'b'),(1,'c');
22. # 查看数据的分布情况
23. mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME in ('t_hash1','t_key1');
24. +------------+----------------+------------+----------------+-------------+
25. | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
26. +------------+----------------+------------+----------------+-------------+
27. | t_hash1    | p0             |          1 |          16384 |       16384 |
28. | t_hash1    | p1             |          1 |          16384 |       16384 |
29. | t_hash1    | p2             |          1 |          16384 |       16384 |
30. | t_key1     | p0             |          0 |              0 |       16384 |
31. | t_key1     | p1             |          2 |           8192 |       16384 |
32. | t_key1     | p2             |          1 |          16384 |       16384 |
33. +------------+----------------+------------+----------------+-------------+
34. 6 rows in set (0.00 sec)
36. mysql> select * from t_hash1 partition(p2);
37. +------+------+
38. | c1   | c2   |
39. +------+------+
40. | NULL | a    |
41. +------+------+
42. 1 row in set (0.00 sec)
44. mysql> select * from t_key1 partition(p2);
45. +------+------+
46. | c1   | c2   |
47. +------+------+
48. | NULL | a    |
49. +------+------+
50. 1 row in set (0.00 sec)

复制代码

可以看到，当hash/key分区表的分区数为3时，分区列为null值的记录分布在了p2分区。
5.总结

range分区表：如果插入记录的分区列值为NULL，则将该行记录插入到最小的分区中。
list分区表：对NULL值的处理有2种方式：
（1）当且仅当只有一个分区使用包含NULL的值做分区表达式时(例如：PARTITION p3 VALUES IN (NULL))，允许插入分区列为NULL的值。
（2）当表中没有显示使用包含NULL的值做分区表达式时，会拒绝插入分区列为NULL的值。
hash/key分区表：对NULL的处理略有不同，不同的分区数，会导致分区列为NULL值的记录分布到不同的分区。

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！