MySQL のパーティショニングには、パーティショニング式の値 (カラム値またはユーザー定義式の値にかかわらず) として NULL
を拒否する手段はありません。式の値として NULL
を使用することは許可されていますが (そうでない場合は整数を返す必要がある)、NULL
は数値でないことを認識することは重要です。MySQL のパーティショニング実装は、ORDER BY
のように、NULL
でない値より小さい値として NULL
を扱います。
これは、NULL
の扱いは各タイプのパーティショニングで異なり、これに準備していない場合は予期しない動作になる可能性があることを意味します。このような状況があるので、このセクションでは、各 MySQL パーティショニングタイプが、行をどのパーティションに格納するべきかを判断するときに NULL
値をどのように扱うかを説明し、それぞれの例を示します。
RANGE パーティショニングでの NULL の扱い
パーティションを判断するために使用されるカラム値が NULL
である行を、RANGE
によってパーティション化されたテーブルに挿入した場合、行はもっとも低いパーティションに挿入されます。p
という名前のデータベースに、次のように作成された 2 つのテーブルがあるとします。
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)
これらの 2 つの CREATE TABLE
ステートメントによって作成されたパーティションについては、次のクエリーを 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 | 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)
(このテーブルについての詳細は、セクション21.13「INFORMATION_SCHEMA PARTITIONS テーブル」を参照してください。)ここで、これらの各テーブルのパーティショニングキーとして使用されるカラムに NULL
が含まれる単一行を移入し、2 つの 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
の詳細については、セクション13.1.7「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
を返します。dt
カラム値が NULL
である行は、パーティショニング式がほかの値より小さい値に評価されたかのように扱われ、パーティション 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
ts1
に挿入できるのは、c1
値が 0
以上 8
以下の行のみです。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 である 2 つの行を 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 のように扱われることを簡単に確認できます。