MySQL磁區表對NULL值的處理-有解無憂

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磁區，