Movatterモバイル変換

Partitioning in MySQL does nothing to disallowNULL as the value of a partitioning expression, whether it is a column value or the value of a user-supplied expression. Even though it is permitted to useNULL as the value of an expression that must otherwise yield an integer, it is important to keep in mind thatNULL is not a number. MySQL's partitioning implementation treatsNULL as being less than any non-NULL value, just asORDER BY does.

This means that treatment ofNULL varies between partitioning of different types, and may produce behavior which you do not expect if you are not prepared for it. This being the case, we discuss in this section how each MySQL partitioning type handlesNULL values when determining the partition in which a row should be stored, and provide examples for each.

Handling of NULL with RANGE partitioning.  If you insert a row into a table partitioned byRANGE such that the column value used to determine the partition isNULL, the row is inserted into the lowest partition. Consider these two tables in a database namedp, created as follows:

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)

You can see the partitions created by these twoCREATE TABLE statements using the following query against thePARTITIONS table in theINFORMATION_SCHEMA database:

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)

(For more information about this table, seeSection 24.3.16, “The INFORMATION_SCHEMA PARTITIONS Table”.) Now let us populate each of these tables with a single row containing aNULL in the column used as the partitioning key, and verify that the rows were inserted using a pair ofSELECT statements:

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)

You can see which partitions are used to store the inserted rows by rerunning the previous query againstINFORMATION_SCHEMA.PARTITIONS and inspecting the output:

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)

You can also demonstrate that these rows were stored in the lowest partition of each table by dropping these partitions, and then re-running theSELECT statements:

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)

(For more information onALTER TABLE ... DROP PARTITION, seeSection 13.1.8, “ALTER TABLE Statement”.)

NULL is also treated in this way for partitioning expressions that use SQL functions. Suppose that we define a table using aCREATE TABLE statement such as this one:

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

As with other MySQL functions,YEAR(NULL) returnsNULL. A row with adt column value ofNULL is treated as though the partitioning expression evaluated to a value less than any other value, and so is inserted into partitionp0.

Handling of NULL with LIST partitioning.  A table that is partitioned byLIST admitsNULL values if and only if one of its partitions is defined using that value-list that containsNULL. The converse of this is that a table partitioned byLIST which does not explicitly useNULL in a value list rejects rows resulting in aNULL value for the partitioning expression, as shown in this example:

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 9mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');ERROR 1504 (HY000): Table has no partition for value NULL

Only rows having ac1 value between0 and8 inclusive can be inserted intots1.NULL falls outside this range, just like the number9. We can create tablests2 andts3 having value lists containingNULL, as shown here:

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)

When defining value lists for partitioning, you can (and should) treatNULL just as you would any other value. For example, bothVALUES IN (NULL) andVALUES IN (1, 4, 7, NULL) are valid, as areVALUES IN (1, NULL, 4, 7),VALUES IN (NULL, 1, 4, 7), and so on. You can insert a row havingNULL for columnc1 into each of the tablests2 andts3:

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)

By issuing the appropriate query against the Information SchemaPARTITIONS table, you can determine which partitions were used to store the rows just inserted (we assume, as in the previous examples, that the partitioned tables were created in thep database):

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)

As shown earlier in this section, you can also verify which partitions were used for storing the rows by deleting these partitions and then performing aSELECT.

Handling of NULL with HASH and KEY partitioning. NULL is handled somewhat differently for tables partitioned byHASH orKEY. In these cases, any partition expression that yields aNULL value is treated as though its return value were zero. We can verify this behavior by examining the effects on the file system of creating a table partitioned byHASH and populating it with a record containing appropriate values. Suppose that you have a tableth (also in thep database) created using the following statement:

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

The partitions belonging to this table can be viewed using the query shown here:

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 for each partition is 0. Now insert two rows intoth whosec1 column values areNULL and 0, and verify that these rows were inserted, as shown here:

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)

Recall that for any integerN, the value ofNULL MODN is alwaysNULL. For tables that are partitioned byHASH orKEY, this result is treated for determining the correct partition as0. Checking the Information SchemaPARTITIONS table once again, we can see that both rows were inserted into partitionp0:

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)

By repeating the last example usingPARTITION BY KEY in place ofPARTITION BY HASH in the definition of the table, you can verify thatNULL is also treated like 0 for this type of partitioning.