MySQL分区表对NULL值的处理
GreatSQL社区
发布于 2023-02-22 02:28:06
发布于 2023-02-22 02:28:06
代码可运行
运行总次数：0
代码可运行
* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
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_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 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 t_range 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: 0
mysql> 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_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 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条数据
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: 0
mysql> 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分区表，分区数为3
CREATE 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 :)
《深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐：
- GreatSQL社区邀您参与——2022中国开源年会
- 简明binlog event解析
- 实现一个简单的Database8(译文)
- 图文结合带你搞懂InnoDB MVCC
- JDK1.7下测试Connector_J连接MySQL8.0
关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
GreatSQL社区官网: https://greatsql.cn/
Gitee: https://gitee.com/GreatSQL/GreatSQL
GitHub: https://github.com/GreatSQL/GreatSQL
Bilibili：
https://space.bilibili.com/1363850082/video
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-12-03，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
![mysql分区表_MySQL分区分表[通俗易懂]](https://ask.qcloudimg.com/http-save/yehe-8223537/46de2bf8fc1c07f52cbc5c01ab84eabd.jpeg)
推荐阅读
相关推荐
MySQL分区表MAXVALUE can only be used in last partition definition错误解决方案
更多 >
💥开发者 MCP广场重磅上线！
精选全网热门MCP server，让你的AI更好用 🚀
腾讯云开发者
