- GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
- GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。
- 作者:王权富贵
1.概述
MySQL的分区表没有禁止NULL值作为分区表达式的值,无论它是列值还是用户提供的表达式的值,需要记住NULL值不是数字。MySQL的分区实现中将NULL视为小于任何非NULL值,与order by类似。
2.range分区表处理NULL
1.创建range分区表
CREATE TABLE t_range (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);
2.插入2条分区列为null值的数据
insert into t_range values (NULL,'a'),(NULL,'b');
3.查看数据的分布情况
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTHFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME = 't_range';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t_range | p0 | 2 | 8192 | 16384 || t_range | p1 | 0 | 0 | 16384 || t_range | p2 | 0 | 0 | 16384 |+------------+----------------+------------+----------------+-------------+3 rows in set (0.01 sec)mysql> select * from t1 partition(p0);+------+------+| c1 | c2 |+------+------+| NULL | a || NULL | b |+------+------+2 rows in set (0.00 sec)
可以看到分区列包含null值的2条数据都分布在p0分区上。
3.list分区表处理NULL
1.创建2张list分区表,t_list1分区列包含null值,t_list2分区列中不包含null值
CREATE TABLE t_list1 (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));CREATE TABLE t_list2 (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));
2.分别向2张表中插入2条分区列为null值的数据
mysql> insert into t_list1 values (NULL,'a'),(NULL,'b');Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into t_list2 values (NULL,'a'),(NULL,'b');ERROR 1526 (HY000): Table has no partition for value NULL
可以看到 t_list2 表的分区列中不包含null值,所以数据插入失败。
3.查看数据的分布情况
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTHFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = 'test1' AND TABLE_NAME = 't_list1';+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t_list1 | p0 | 0 | 0 | 16384 || t_list1 | p1 | 0 | 0 | 16384 || t_list1 | p2 | 0 | 0 | 16384 || t_list1 | p3 | 2 | 8192 | 16384 |+------------+----------------+------------+----------------+-------------+4 rows in set (0.00 sec)
可以看到 t_list1 表中插入的2条包含null值的数据,由于p3分区包含null值列,所以2条数据分布在p3分区中。
4.hash/key分区表处理NULL
1.创建2张测试表,一张hash分区表,一张key分区表
CREATE TABLE t_hash (c1 INT,c2 VARCHAR(20))PARTITION BY HASH(c1)PARTITIONS 2;CREATE TABLE t_key (c1 INT,c2 VARCHAR(20))PARTITION BY key(c1)PARTITIONS 2;
2.分别向2张表中插入3条分区列为null值的数据
mysql> insert into t_hash values (NULL,'a'),(0,'b'),(1,'c');Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> insert into t_key values (NULL,'a'),(0,'b'),(1,'c');Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0
3.查看数据的分布情况
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');+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t_hash | p0 | 2 | 8192 | 16384 || t_hash | p1 | 1 | 16384 | 16384 || t_key | p0 | 2 | 8192 | 16384 || t_key | p1 | 1 | 16384 | 16384 |+------------+----------------+------------+----------------+-------------+4 rows in set (0.00 sec)mysql> select * from t_hash partition(p0);+------+------+| c1 | c2 |+------+------+| NULL | a || 0 | b |+------+------+2 rows in set (0.00 sec)mysql> select * from t_key partition(p0);+------+------+| c1 | c2 |+------+------+| NULL | a || 1 | c |+------+------+2 rows in set (0.00 sec)
可以看到分区列中包含null值的记录都在p0分区。
4.如果我们增加hash/key分区表的分区数,分区列为null值的记录会分布到其他分区
# 创建hash/key分区表,分区数为3CREATE TABLE t_hash1 (c1 INT,c2 VARCHAR(20))PARTITION BY HASH(c1)PARTITIONS 3;CREATE TABLE t_key1 (c1 INT,c2 VARCHAR(20))PARTITION BY key(c1)PARTITIONS 3;# 插入数据insert into t_hash1 values (NULL,'a'),(0,'b'),(1,'c');insert into t_key1 values (NULL,'a'),(0,'b'),(1,'c');# 查看数据的分布情况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');+------------+----------------+------------+----------------+-------------+| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |+------------+----------------+------------+----------------+-------------+| t_hash1 | p0 | 1 | 16384 | 16384 || t_hash1 | p1 | 1 | 16384 | 16384 || t_hash1 | p2 | 1 | 16384 | 16384 || t_key1 | p0 | 0 | 0 | 16384 || t_key1 | p1 | 2 | 8192 | 16384 || t_key1 | p2 | 1 | 16384 | 16384 |+------------+----------------+------------+----------------+-------------+6 rows in set (0.00 sec)mysql> select * from t_hash1 partition(p2);+------+------+| c1 | c2 |+------+------+| NULL | a |+------+------+1 row in set (0.00 sec)mysql> select * from t_key1 partition(p2);+------+------+| c1 | c2 |+------+------+| NULL | a |+------+------+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 ?
关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。
相关链接: GreatSQL社区GiteeGitHubBilibili
GreatSQL社区:
捉虫活动详情:https://greatsql.cn/thread-97-1-1.html
社区博客有奖征稿详情:https://greatsql.cn/thread-100-1-1.html
技术交流群:
微信:扫码添加
GreatSQL社区助手
微信好友,发送验证信息加群
。
)