mysql按时间范围分区

本篇文章向大家介绍《mysql按时间范围分区》，主要包括MySQL、数据库、NoSQL、MariaDB，具有一定的参考价值，需要的朋友可以参考一下。

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

理论要掌握，实操不能落！以上关于《mysql按时间范围分区》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！