mysql按章_mysql按时间范围分区

1、问题描述

mysql的开发人员经常按时间范围分区不知道时间上怎么写，笔者把总结归纳下来，能帮助自已和分享成果。

2、解决问题

mysql对时间范围分区，可以是按年，按月，按天，按分，按秒，如下案例，这里注意点：范围分区是一个开始是开区间，结束是闭区间：[0,10),[10,20),[20,30).....

2.1、按年

CREATE TABLE sales_year

(DATE DATETIME

)ENGINE = INNODB

PARTITION BY RANGE(YEAR(DATE))

(PARTITION p2020 VALUES LESS THAN (2020),

PARTITION p2021 VALUES LESS THAN (2021),

PARTITION p2022 VALUES LESS THAN (2022),

PARTITION pmax VALUES LESS THAN (maxvalue)

);

2.1、按月CREATE TABLE sales_month

(DATE DATETIME

)ENGINE = INNODB

PARTITION BY RANGE(MONTH(DATE))

(PARTITION p202001 VALUES LESS THAN (202001),

PARTITION p202002 VALUES LESS THAN (202002),

PARTITION p202003 VALUES LESS THAN (202003),

PARTITION pmax VALUES LESS THAN (