GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本，使用上與MySQL一致。 作者：王權富貴 1.概述 MySQL的分區表沒有禁止NULL值作為分區表達式的值，無論它是列值還是用戶提供的表達式的值，需要記住NULL值不是數字。My ...
- 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_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 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: 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條分區列為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: 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
