mysql 分区建表

-- 按日分区
CREATE TABLE `table_name` (
	datadate DATETIME DEFAULT NULL
	) ENGINE = INNODB 
PARTITION BY RANGE (to_days(`datadate`)) 
(
	PARTITION p0 VALUES less than (to_days('2019-01-01')),
	PARTITION p1 VALUES less than (to_days('2019-01-02')),
	PARTITION p2 VALUES less than MAXVALUE
);

-- 按月分区
CREATE TABLE `table_name` (
	datadate DATETIME DEFAULT NULL
	) ENGINE = INNODB 
PARTITION BY RANGE (to_days(`datadate`)) 
(
	PARTITION p0 VALUES less than (to_days('2019-01-01')),
	PARTITION p1 VALUES less than (to_days('2019-02-01')),
	PARTITION p2 VALUES less than MAXVALUE
);

-- 按年分区
CREATE TABLE `table_name` (
	datadate DATETIME DEFAULT NULL
	) ENGINE = INNODB 
PARTITION BY RANGE (to_days(`datadate`)) 
(
	PARTITION p0 VALUES less than (YEAR('2019-01-01')),
	PARTITION p1 VALUES less than (YEAR('2020-02-01')),
	PARTITION p2 VALUES less than MAXVALUE
);

-- 新增分区
ALTER TABLE table_name ADD PARTITION (PARTITION p_name VALUES LESS THAN (xxxxxxxxx));