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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 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 的 dt 欄位值的資料列會被視為分割表達式評估為小於任何其他值的值，因此會插入到分割 p0 中。

使用 LIST 分割處理 NULL。 如果 LIST 分割的表格僅在其分割之一定義使用包含 NULL 的值列表時，才接受 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 HASH 替換為 PARTITION BY KEY 來重複上一個範例，您可以驗證對於這種分割類型，NULL 也被視為 0。