关于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 (maxvalue)

);
2.2、按天

CREATE TABLE sales_day

(

  DATE DATETIME

)ENGINE = INNODB
PARTITION BY RANGE(TO_DAYS(DATE))
(

 PARTITION p20200101 VALUES LESS THAN (20200101),
 PARTITION p20200202 VALUES LESS THAN (20200102),
 PARTITION p20200203 VALUES LESS THAN (20200103),
 PARTITION pmax VALUES LESS THAN (maxvalue)

);
2.3、按分
CREATE TABLE sales_second
(

   date timestamp

)ENGINE = INNODB
PARTITION BY RANGE(TO_SECONDS(DATE))
(

 PARTITION p1 VALUES LESS THAN (63769153669),
 PARTITION P2 VALUES LESS THAN (63769153734),
 PARTITION p3 VALUES LESS THAN (63769153752),
 PARTITION pmax VALUES LESS THAN (maxvalue)

);
2.4、按秒
CREATE TABLE sales_timestamp
(

id INT,
DATE TIMESTAMP

)
PARTITION BY RANGE (UNIX_TIMESTAMP(DATE) )
(

PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-02 00:00:00') ),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-03 00:00:00') ),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-04 00:00:00') ),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-05 00:00:00') ),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-06 00:00:00') ),
PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-07 00:00:00') ),
PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-08 00:00:00') ),
PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-09 00:00:00') ),
PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-10 00:00:00') ),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-11 00:00:00') ),
PARTITION pmax VALUES LESS THAN (maxvalue)

);

INSERT INTO sales_timestamp(ID,DATE) VALUES(1,CURRENT_TIMESTAMP());
INSERT INTO sales_timestamp(ID,DATE) VALUES(2,CURRENT_TIMESTAMP());
INSERT INTO sales_timestamp(ID,DATE) VALUES(3,CURRENT_TIMESTAMP());
INSERT INTO sales_timestamp(ID,DATE) VALUES(4,CURRENT_TIMESTAMP());
INSERT INTO sales_timestamp(ID,DATE) VALUES(5,CURRENT_TIMESTAMP());
INSERT INTO sales_timestamp(ID,DATE) VALUES(5,’2020-10-05 00:00:00′);