• 子表分区，是在表分区的基础上再创建表分区的概念，每个表分区下的子表分区个数必须一致
mysql> CREATE TABLE ts (id INT, purchased DATE) -> PARTITION BY RANGE( YEAR(purchased) ) -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) -> SUBPARTITIONS 2 -> ( PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (2000), -> PARTITION p2 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.48 sec) mysql> desc information_schema.partitions; +-------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(64) | NO | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | NULL | | | TABLE_NAME | varchar(64) | NO | | NULL | | | PARTITION_NAME | varchar(64) | YES | | NULL | | | SUBPARTITION_NAME | varchar(64) | YES | | NULL | | | PARTITION_ORDINAL_POSITION | int(6) unsigned | YES | | NULL | | | SUBPARTITION_ORDINAL_POSITION | int(6) unsigned | YES | | NULL | | | PARTITION_METHOD | varchar(13) | YES | | NULL | | | SUBPARTITION_METHOD | varchar(13) | YES | | NULL | | | PARTITION_EXPRESSION | varchar(2048) | YES | | NULL | | | SUBPARTITION_EXPRESSION | varchar(2048) | YES | | NULL | | | PARTITION_DESCRIPTION | text | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | timestamp | NO | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | PARTITION_COMMENT | text | NO | | NULL | | | NODEGROUP | varchar(256) | YES | | NULL | | | TABLESPACE_NAME | varchar(259) | YES | | NULL | | +-------------------------------+---------------------+------+-----+---------+-------+ 25 rows in set (0.01 sec) mysql> select partition_name,subpartition_name from information_schema.partitions where table_name='ts'; +----------------+-------------------+ | PARTITION_NAME | SUBPARTITION_NAME | +----------------+-------------------+ | p0 | p0sp0 | | p0 | p0sp1 | | p1 | p1sp0 | | p1 | p1sp1 | | p2 | p2sp0 | | p2 | p2sp1 | +----------------+-------------------+ 6 rows in set (0.00 sec) mysql> select to_days(now()); +----------------+ | to_days(now()) | +----------------+ | 737917 | +----------------+ 1 row in set (0.00 sec) mysql> desc ts; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | purchased | date | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into ts values(1,now()); Query OK, 1 row affected, 1 warning (0.10 sec) mysql> insert into ts values(1,'1980-01-01'); Query OK, 1 row affected (0.09 sec) mysql> select * from ts partition(p1); Empty set (0.00 sec) mysql> select * from ts partition(p2); +------+------------+ | id | purchased | +------+------------+ | 1 | 2020-05-07 | +------+------------+ 1 row in set (0.00 sec) mysql> select * from ts partition(p0sp0); +------+------------+ | id | purchased | +------+------------+ | 1 | 1980-01-01 | +------+------------+ 1 row in set (0.00 sec) mysql> select * from ts partition(p0sp1); Empty set (0.00 sec) mysql> select purchased,to_days(purchased) from ts; +------------+--------------------+ | purchased | to_days(purchased) | +------------+--------------------+ | 1980-01-01 | 723180 | | 2020-05-07 | 737917 | +------------+--------------------+ 2 rows in set (0.00 sec) mysql> select * from ts partition(p2sp0); Empty set (0.00 sec) mysql> select * from ts partition(p2sp0); Empty set (0.00 sec) mysql> select * from ts partition(p2); +------+------------+ | id | purchased | +------+------------+ | 1 | 2020-05-07 | +------+------------+ 1 row in set (0.00 sec) mysql> explain select * from ts where purchased='2020-05-07'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ts | p2_p2sp1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
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.18 sec) mysql> select * from t1; Empty set (0.01 sec) mysql> insert into ti values(-1,'a'); ERROR 1146 (42S02): Table 'course.ti' doesn't exist mysql> insert into t1 values(-1,'a'); Query OK, 1 row affected (0.05 sec) mysql> select * from t1 partition(p0); +------+------+ | c1 | c2 | +------+------+ | -1 | a | +------+------+ 1 row in set (0.01 sec) mysql> insert into t1 values(null,'b'); Query OK, 1 row affected (0.04 sec) mysql> select * from t1; +------+------+ | c1 | c2 | +------+------+ | -1 | a | | NULL | b | +------+------+ 2 rows in set (0.00 sec) mysql> select * from t1 partition(p0); +------+------+ | c1 | c2 | +------+------+ | -1 | a | | NULL | b | +------+------+ 2 rows in set (0.00 sec) mysql> select * from t1 partition(p2); Empty set (0.01 sec)
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.26 sec) mysql> insert into ts1 values(null,'a'); ERROR 1526 (HY000): Table has no partition for value NULL mysql> insert into ts1 values(10,'a'); ERROR 1526 (HY000): Table has no partition for value 10 mysql> mysql> mysql> drop table ts1; Query OK, 0 rows affected (0.09 sec) 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,null), -> PARTITION p2 VALUES IN (2, 5, 8)); Query OK, 0 rows affected (0.13 sec) mysql> insert into ts1 values(null,'a'); Query OK, 1 row affected (0.07 sec) mysql> select * from ts1 partition(p1); +------+------+ | c1 | c2 | +------+------+ | NULL | a | +------+------+ 1 row in set (0.00 sec)
mysql> CREATE TABLE th ( c1 INT, c2 VARCHAR(20) ) -> PARTITION BY HASH(c1) -> PARTITIONS 2; Query OK, 0 rows affected (0.18 sec) mysql> insert into th values(null,'a'); Query OK, 1 row affected (0.01 sec) mysql> select * from th; +------+------+ | c1 | c2 | +------+------+ | NULL | a | +------+------+ 1 row in set (0.00 sec) mysql> select * from th partition(p1); Empty set (0.00 sec)