mysql - create partition by date range

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'))
  );

Thống kê các bảng có dữ liệu lớn trong mysql

Thống kê các bảng có dữ liệu lớn trong mysql

Rất hữu ích khi cần thống kê để đánh partion.

SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024 / 1024 ),4) as `Size(G)` FROM information_schema.TABLES WHERE table_schema = 'your_db_name' ORDER BY DATA_LENGTH DESC;