代码改变世界

【MySQL】MySQL分区是如何处理NULL的

  abce  阅读(158)  评论(0)  编辑  收藏  举报

MySQL的分区实现中，认为NULL比任何non-NULL值都小。和order by类似。

​

RANGE分区是如何处理NULL的？
如果将行插入到按RANGE分区的表中，使得用于确定分区的列值为NULL，则该行将插入到最低分区中。假设数据库abce中有两个分区表：

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

查看一下分区表的信息：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |       16384 |
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p0             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)
 
mysql>

插入带有NULL的记录：

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

查看一下被插入了哪个分区：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |          16384 |       16384 |
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p0             |          1 |          16384 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)
 
mysql>

　　

也可以通过删除最小的分区来确认上面插入的两条记录被存储最小的分区了：

?
1
2
3
4
5
6
7
8
9
10
11
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)

对于使用SQL函数的分区表达式，也会以这种方式处理NULL。假设我们定义一个表，例如：

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

YEAR(NULL)返回NULL。如果插入dt为NULL的行，也是被插入到最小分区p0。

 

LIST分区是如何处理NULL的？

当且仅当使用包含NULL的值列表定义其分区之一时，LIST分区的表允许NULL值。与此相反的是，LIST分区的表未在值列表中显式使用NULL会拒绝导致分区表达式为NULL值的行，如下例所示：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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之间，才可以将null值插入表ts1。

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)和VALUES IN(1, 4, 7, NULL)都是有效的，VALUES IN(1, NULL, 4, 7), VALUES IN(NULL, 1, 4, 7), 等等。可以在每个表ts2和ts3中插入一行c1为NULL的行：

?
1
2
3
4
5
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)

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
5 rows in set (0.00 sec)
 
mysql>

　　

HASH和KEY分区是如何处理NULL的？

对于按HASH或KEY分区的表，NULL的处理方式略有不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零。我们可以通过检查创建按HASH分区的表并使用包含适当值的记录填充它对文件系统的影响来验证此行为。假设有一个使用以下语句创建的表th：

?
1
2
3
4
5
6
7
mysql> CREATE TABLE th (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 'th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |       16384 |
| th         | p1             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)
 
mysql>

 

插入测试记录：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

　　

NULL MOD N的值总是NULL。在hash和key分区中，是将其放在分区0中。

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

 

 

本文地址：【MySQL】MySQL分区是如何处理NULL的 - abce - 博客园 (cnblogs.com)

相关博文：
· Oracle、PostgreSQL、SQL Server中的NULL和空字符串的比较
· MySQL使用分区快速归档数据
· MySQL分区表对NULL值的处理
· MySQL8-中文参考-四十五-
· MYSQL默认值null问题情况梳理
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛