content_image/855664.jpg
This means that treatment of NULL  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 handles NULL  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 by RANGE  such that the column value used to determine the partition is NULL , the row is inserted into the lowest partition. Consider these two tables in a database named p , 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 two CREATE TABLE  statements using the following query against the PARTITIONS  table in the INFORMATION_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_’; 

<img src='content_image/855664.jpg'>

(For more information about this table, see Section 26.3.21, “The INFORMATION_SCHEMA PARTITIONS Table” .) Now let us populate each of these tables with a single row containing a NULL  in the column used as the partitioning key, and verify that the rows were inserted using a pair of SELECT  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 | +------+--------+ 

