PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) ); 

Some syntactical items of note are listed here: 

•Each partition must have the same number of subpartitions. 

•If you explicitly define any subpartitions using SUBPARTITION  on any partition of a partitioned table, you must define them all. In other words, the following statement fails: 

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3 ) ); 

This statement would still fail even if it used SUBPARTITIONS 2 . 

•Each SUBPARTITION  clause must include (at a minimum) a name for the subpartition. Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option. 

•Subpartition names must be unique across the entire table. For example, the following CREATE TABLE statement is valid: 

## 24.2.7 How MySQL Partitioning Handles NULL 

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) ); 

Partitioning in MySQL does nothing to disallow NULL  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 use NULL  as the value of an expression that must otherwise yield an integer, it is important to keep in mind that NULL  is not a number. MySQL’s partitioning implementation treats NULL  as being less than any non- NULL  value, just as ORDER BY  does. 

