mysql 分区建表

最新推荐文章于 2025-11-25 13:11:49 发布

原创最新推荐文章于 2025-11-25 13:11:49 发布 · 108 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

-- 按日分区
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));