- 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 ??
關于 GreatSQL
GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，
相關鏈接： GreatSQL社區 Gitee GitHub Bilibili
GreatSQL社區：
捉蟲活動詳情：https://greatsql.cn/thread-97-1-1.html
社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼添加
GreatSQL社區助手微信好友，發送驗證資訊加群，
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/539271.html
標籤：MySQL
下一篇：第十一章-并發控制