MySQL :: MySQL 8.4 參考手冊 :: 26.2.7 MySQL 分割區如何處理 NULL

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / MySQL 分割區如何處理 NULL

26.2.7 MySQL 分割區如何處理 NULL

MySQL 中的分割區不會禁止 NULL 作為分割區運算式的值，無論它是資料行值還是使用者提供的運算式的值。即使允許使用 NULL 作為必須產生整數的運算式的值，但請務必記住 NULL 不是數字。MySQL 的分割區實作會將 NULL 視為小於任何非 NULL 值，就像 ORDER BY 一樣。

這表示不同類型的分割區對 NULL 的處理方式有所不同，而且如果您沒有做好準備，可能會產生您不希望看到的行為。在這種情況下，我們將在本節討論每種 MySQL 分割區類型在決定儲存資料列的分割區時如何處理 NULL 值，並為每一種提供範例。

使用 RANGE 分割區處理 NULL。 如果您將資料列插入依 RANGE 分割區的表格中，使得用於決定分割區的資料行值為 NULL，則該資料列會插入最低的分割區中。請考慮在名為 p 的資料庫中的這兩個表格，如下所示建立

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)

您可以使用以下針對 INFORMATION_SCHEMA 資料庫中的 PARTITIONS 表格的查詢，查看這兩個 CREATE TABLE 陳述式所建立的分割區

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |           0 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          0 |              0 |           0 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

（如需關於此表格的詳細資訊，請參閱第 28.3.21 節「INFORMATION_SCHEMA PARTITIONS 表格」。）現在讓我們用在作為分割區鍵使用的資料行中包含 NULL 的單一資料列來填入這些表格，並使用一對 SELECT 陳述式來驗證是否已插入資料列

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)

您可以透過重新執行先前針對 INFORMATION_SCHEMA.PARTITIONS 的查詢並檢查輸出，來查看哪些分割區用於儲存插入的資料列

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |             20 |          20 |
| t1         | p1             |          0 |              0 |           0 |
| t1         | p2             |          0 |              0 |           0 |
| t2         | p0             |          1 |             20 |          20 |
| t2         | p1             |          0 |              0 |           0 |
| t2         | p2             |          0 |              0 |           0 |
| t2         | p3             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

您也可以透過捨棄這些分割區，然後重新執行 SELECT 陳述式，來證明這些資料列已儲存在每個表格中編號最低的分割區中

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)

（如需 ALTER TABLE ... DROP PARTITION 的詳細資訊，請參閱第 15.1.9 節「ALTER TABLE 陳述式」。）

對於使用 SQL 函數的分割區運算式，NULL 也會以這種方式處理。假設我們使用如下的 CREATE TABLE 陳述式來定義表格

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
);

與其他 MySQL 函數一樣，YEAR(NULL) 會傳回 NULL。資料行值為 NULL 的資料列會被視為分割區運算式評估為小於任何其他值的值，因此會插入分割區 p0 中。

使用 LIST 分割區處理 NULL。 如果且僅當其其中一個分割區是使用包含 NULL 的值清單定義時，依 LIST 分割區的表格才會接受 NULL 值。反之，依 LIST 分割區且未在值清單中明確使用 NULL 的表格會拒絕導致分割區運算式產生 NULL 值的資料列，如此範例所示

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 （包含）之間的資料列才能插入 ts1 中。NULL 落在這個範圍之外，就像數字 9 一樣。我們可以建立包含 NULL 的值清單的表格 ts2 和 ts3，如下所示

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) 等等也是如此。您可以將 c1 資料行包含 NULL 的資料列插入每個表格 ts2 和 ts3 中

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)

藉由針對 INFORMATION_SCHEMA.PARTITIONS 發出適當的查詢，您可以決定哪些分割區用於儲存剛插入的資料列（我們假設，在先前的範例中，分割區表格是在 p 資料庫中建立的）

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2        | p0             |          0 |              0 |           0 |
| ts2        | p1             |          0 |              0 |           0 |
| ts2        | p2             |          0 |              0 |           0 |
| ts2        | p3             |          1 |             20 |          20 |
| ts3        | p0             |          0 |              0 |           0 |
| ts3        | p1             |          1 |             20 |          20 |
| ts3        | p2             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

如本節先前所示，您也可以透過刪除這些分割區，然後執行 SELECT 來驗證哪些分割區用於儲存資料列。

使用 HASH 和 KEY 分割區處理 NULL。 對於依 HASH 或 KEY 分割區的表格，NULL 的處理方式略有不同。在這些情況下，任何產生 NULL 值的分割區運算式都會被視為其傳回值為零。我們可以透過檢查建立依 HASH 分割區的表格並使用包含適當值的記錄來填入它對檔案系統的影響，來驗證此行為。假設您有一個使用以下陳述式建立的表格 th （也在 p 資料庫中）

mysql> CREATE TABLE th (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

可以使用此處顯示的查詢來檢視屬於此表格的分割區

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |           0 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

每個分割區的 TABLE_ROWS 都是 0。現在將兩個 c1 資料行值為 NULL 和 0 的資料列插入 th 中，並驗證是否已插入這些資料列，如下所示

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)

請記住，對於任何整數 N，NULL MOD N 的值永遠是 NULL。對於依 HASH 或 KEY 分割區的表格，此結果在決定正確的分割區時會被視為 0。再次檢查 Information Schema PARTITIONS 表格，我們可以發現這兩個資料列都插入了分割區 p0

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
     >   FROM INFORMATION_SCHEMA.PARTITIONS
     >   WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          2 |             20 |          20 |
| th         | p1             |          0 |              0 |           0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

藉由在表格的定義中使用 PARTITION BY KEY 來取代 PARTITION BY HASH 來重複最後一個範例，您可以驗證對於這種分割區類型，NULL 也會被視為 0。