mysql> CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1', PARTITION p1 VALUES LESS THAN ('2019-1-1') ENGINE = InnoDB;

mysql> select * from information_schema.partitions where table_schema='test' and table_name='test'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: test PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE COLUMNS SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: create_date SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: '2018-1-1' TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2019-01-01 16:03:02 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: test PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE COLUMNS SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: create_date SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: '2019-1-1' TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2019-01-01 16:03:02 UPDATE_TIME: 2019-01-01 16:03:58 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 2 rows in set (0.00 sec)

mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1') ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN ('2019-1-1') ENGINE = InnoDB) */ | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

mysql> alter table test add partition(partition paaa values less than maxvalue); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1') ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN ('2019-1-1') ENGINE = InnoDB, PARTITION paaa VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

mysql> alter table test add partition(partition p2 values less than('2019-2-1')); 出现以下错误，说明只能从最大的partition 后开始添加，因为partiton paaa 的值是最大值，所以会出现错误 ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

#将最大的分区，拆分为其它分区 mysql> alter table test reorganize partition paaa into(partition p2 values less than('2019-2-1'),partition p3 values less than maxvalue); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1') ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN ('2019-1-1') ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN ('2019-2-1') ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> insert into test(id,uname,create_date)values(2,'aaa','2019-2-2');
Query OK, 1 row affected (0.04 sec)

mysql> insert into test(id,uname,create_date)values(2,'aaa','2019-3-1'); Query OK, 1 row affected (0.09 sec)

mysql> select count(1) from test; +----------+ | count(1) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec)

mysql> checksum table test; +-----------+------------+ | Table | Checksum | +-----------+------------+ | test.test | 1892330486 | +-----------+------------+ 1 row in set (0.01 sec)

mysql> explain select * from test where create_date='2018-3-1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)

mysql> explain select * from test where create_date='2018-2-2'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test where create_date='2018-2-1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test where create_date='2018-2-1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)

mysql> explain select * from test where create_date='2018-1-1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> select * from test; +------+-------+-------------+ | id | uname | create_date | +------+-------+-------------+ | 1 | aaaa | 2017-12-10 | | 1 | aaaa | 2017-01-01 | | 2 | aaa | 2018-10-02 | | 2 | aaa | 2019-01-02 | | 2 | aaa | 2019-02-02 | | 2 | aaa | 2019-03-01 | +------+-------+-------------+ 6 rows in set (0.00 sec)

mysql> explain select * from test where create_date='2019-2-1'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p3 | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> checksum table test; +-----------+------------+ | Table | Checksum | +-----------+------------+ | test.test | 1892330486 | +-----------+------------+ 1 row in set (0.00 sec) #合并分区到一个分区 mysql> alter table test reorganize partition p1,p2,p3 into(partition p3 values less than maxvalue); mysql> explain select * from test where create_date='2019-1-1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

mysql> show create table test; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1') ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) / | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 再一次做拆分 mysql> alter table test reorganize partition p3 into(partition p1 values less than ('2019-1-1'),partition p2 values less than('2019-2-1'),partition p3 values less than maxvalue); mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE test ( id int(11) DEFAULT NULL, uname varchar(50) DEFAULT NULL, create_date date NOT NULL DEFAULT '1970-01-01' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /!50500 PARTITION BY RANGE COLUMNS(create_date) (PARTITION p0 VALUES LESS THAN ('2018-1-1') ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN ('2019-1-1') ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN ('2019-2-1') ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */ | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

删除分区，会删除在该分区的数据 mysql> alter table test drop partition p2; mysql> select * from test; +------+-------+-------------+ | id | uname | create_date | +------+-------+-------------+ | 1 | aaaa | 2017-12-10 | | 1 | aaaa | 2017-01-01 | | 2 | aaa | 2018-10-02 | | 2 | aaa | 2019-02-02 | | 2 | aaa | 2019-03-01 | +------+-------+-------------+