MySQL分区表对NULL值的处理

fifalv

发布于 2023-1-18 11:23

浏览

0收藏

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
);1.
2.
3.
4.
5.
6.
7.
8.
9.

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

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

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

可以看到分区列包含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)
);1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

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 NULL1.
2.
3.
4.
5.

可以看到 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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

可以看到 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;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

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: 01.
2.
3.
4.
5.
6.

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

可以看到分区列中包含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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

可以看到，当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值的记录分布到不同的分区。

本文转载自公众号：GreatSQL社区

分类

数据库

标签

MySQL

已于2023-1-18 11:23:29修改

51CTO

51CTO博客

51CTO学堂

MySQL分区表对NULL值的处理

1.概述

2.range分区表处理NULL

3.list分区表处理NULL

4.hash/key分区表处理NULL

5.总结

目录

订阅鸿蒙技术特刊，精选内容抢先看