CREATE TABLE `register_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , `msisdn` varchar(15) NOT NULL , `reg_time` datetime NULL DEFAULT NULL , PRIMARY KEY (`id`,`reg_time`), INDEX `msisdn_idx` (`msisdn`, `reg_time`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PARTITION BY RANGE( TO_DAYS(reg_time) ) ( PARTITION p20160601 VALUES LESS THAN (TO_DAYS('2016-06-01')), PARTITION p20160701 VALUES LESS THAN (TO_DAYS('2016-07-01')), PARTITION p20160801 VALUES LESS THAN (TO_DAYS('2016-08-01')), PARTITION p20160901 VALUES LESS THAN (TO_DAYS('2016-09-01')), PARTITION p20161001 VALUES LESS THAN (TO_DAYS('2016-10-01')), PARTITION p20161101 VALUES LESS THAN (TO_DAYS('2016-11-01')), PARTITION p20161201 VALUES LESS THAN (TO_DAYS('2016-12-01')), PARTITION p20170101 VALUES LESS THAN (TO_DAYS('2017-01-01')), PARTITION p20170201 VALUES LESS THAN (TO_DAYS('2017-02-01')), PARTITION p20170301 VALUES LESS THAN (TO_DAYS('2017-03-01')), PARTITION p20170401 VALUES LESS THAN (TO_DAYS('2017-04-01')), PARTITION p20170501 VALUES LESS THAN (TO_DAYS('2017-05-01')), PARTITION p20170601 VALUES LESS THAN (TO_DAYS('2017-06-01')), PARTITION p20170701 VALUES LESS THAN (TO_DAYS('2017-07-01')), PARTITION p20170801 VALUES LESS THAN (TO_DAYS('2017-08-01')), PARTITION p20170901 VALUES LESS THAN (TO_DAYS('2017-09-01')), PARTITION p20171001 VALUES LESS THAN (TO_DAYS('2017-10-01')), PARTITION p20171101 VALUES LESS THAN (TO_DAYS('2017-11-01')), PARTITION p20171201 VALUES LESS THAN (TO_DAYS('2017-12-01')), PARTITION p20180101 VALUES LESS THAN (TO_DAYS('2018-01-01')), PARTITION p20180201 VALUES LESS THAN (TO_DAYS('2018-02-01')), PARTITION p20180301 VALUES LESS THAN (TO_DAYS('2018-03-01')), PARTITION p20180401 VALUES LESS THAN (TO_DAYS('2018-04-01')), PARTITION p20180501 VALUES LESS THAN (TO_DAYS('2018-05-01')), PARTITION p20180601 VALUES LESS THAN (TO_DAYS('2018-06-01')), PARTITION p20180701 VALUES LESS THAN (TO_DAYS('2018-07-01')), PARTITION p20180801 VALUES LESS THAN (TO_DAYS('2018-08-01')), PARTITION p20180901 VALUES LESS THAN (TO_DAYS('2018-09-01')), PARTITION p20181001 VALUES LESS THAN (TO_DAYS('2018-10-01')), PARTITION p20181101 VALUES LESS THAN (TO_DAYS('2018-11-01')), PARTITION p20181201 VALUES LESS THAN (TO_DAYS('2018-12-01')), PARTITION p20190101 VALUES LESS THAN (TO_DAYS('2019-01-01')), PARTITION p20190201 VALUES LESS THAN (TO_DAYS('2019-02-01')), PARTITION p20190301 VALUES LESS THAN (TO_DAYS('2019-03-01')), PARTITION p20190401 VALUES LESS THAN (TO_DAYS('2019-04-01')), PARTITION p20190501 VALUES LESS THAN (TO_DAYS('2019-05-01')), PARTITION p20190601 VALUES LESS THAN (TO_DAYS('2019-06-01')), PARTITION p20190701 VALUES LESS THAN (TO_DAYS('2019-07-01')), PARTITION p20190801 VALUES LESS THAN (TO_DAYS('2019-08-01')), PARTITION p20190901 VALUES LESS THAN (TO_DAYS('2019-09-01')), PARTITION p20191001 VALUES LESS THAN (TO_DAYS('2019-10-01')), PARTITION p20191101 VALUES LESS THAN (TO_DAYS('2019-11-01')), PARTITION p20191201 VALUES LESS THAN (TO_DAYS('2019-12-01')) );
Alter Table Partition Example:
ReplyDeleteALTER TABLE `lottery_results_pa` PARTITION BY RANGE( YEAR(`date`) )
(
PARTITION p_2026 VALUES LESS THAN (2027),
PARTITION p_2027 VALUES LESS THAN (2028)
);
Show partition info:
ReplyDeleteSELECT PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, CREATE_TIME
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tb_name';