假设现在有这样一个需求: 数据库中有一张产品订单表,表中有Date类型的字段CREATE_DATE代表订单创建的日期,要求获取创建日期为3月25号到4月24号之间的订单.很明显,这个区间对应一个月的跨度,但是并不是自然月,这个需求该怎么实现呢?为了说明如何解决该需求,先创建表ProductOrder.

ProductOrder

使用以下脚本创建表并插入一些原始数据:

CREATE TABLE IF NOT EXISTS ProductOrder(
 
 ID int(11) AUTO_INCREMENT COMMENT '订单表主键',
 
 ORDER_ID bigint(64) NOT NULL COMMENT '订单表唯一业务ID',
 
 PRODUCT_ID bigint(64) NOT NULL COMMENT '产品唯一编号ID',
 
 CREATE_DATE date NOT NULL COMMENT '订单创建时间',
 
 CREATE_TIMESTAMP timestamp NOT NULL default current_timestamp COMMENT '订单创建时间戳',
 
 PRIMARY KEY(ID)
 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-24','2018-03-24 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-25','2018-03-25 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-26','2018-03-26 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-03-27','2018-03-27 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-21','2018-04-21 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-23','2018-04-23 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-24','2018-04-24 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-25','2018-04-25 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-26','2018-04-26 23:33:33');
insert into ProductOrder(ORDER_ID,PRODUCT_ID,CREATE_DATE,CREATE_TIMESTAMP) values('1','1','2018-04-27','2018-04-27 23:33:33');

相关函数

DATE_SUB

DATE_SUB用于减去指定的日期,可以按日减,按月减,按年减等.它的语法是这样的DATE_SUB(date,INTERVAL expr type),date代表原始日期,比如2018-04-23.INTERVAL是固定值,expr代表减去的数目,type代表减去的时间单位,比如减去一个月的表达式就是DATE_SUB('2018-04-23',INTERVAL 1 MONTH),得到的结果是2018-03-23.更多可以使用的单位和细节参考MySQL DATE_SUB() 函数.另外,如果是加上一定的时间,可以使用DATE_ADD函数.

LEFT

mysql可以使用left(), right(), substring(), substring_index()等字符串截取函数截取字符串.这里以left为例,它的表达式是left(str,length).str是待截取的字符串,这里假设是2018-03-23,length代表从左边开始数几位开始截取,假设length为8,最终表达式为left('2018-03-23','8'),最终截取以后得到的字符串就是2018-03-.关于字符串截取参考MySQL字符串函数substring：字符串截取

CONCAT

mysql中可以使用CONCAT函数拼接多个字符串,比如要把刚刚截取的字符串和25拼接到一块的表达式就是CONCAT('2018-03-','25'),这样就得到了2018-03-25.关于CONCAT参考mysql函数之四：concat() mysql 多个字段拼接和SQL中字符串拼接.补充一点,oracle和db2中可以使用||拼接字符串,mysql中不行.

最终方案

方案概述

先使用CURRENT_DATE获取当前日期,然后使用DATE_SUB减去一个月,LEFT裁剪8位再拼接上25,这样得到了区间的最小日期.再使用CURRENT_DATE获取当前日期,LEFT裁剪8位再拼接上24,这样得到了区间的最大日期.

最终的sql

根据上面的思路得到的sql如下:

SELECT * from ProductOrder WHERE CREATE_DATE>=CONCAT(LEFT(DATE_SUB(CURRENT_DATE,INTERVAL 1 MONTH),'8'),'25')
 
AND CREATE_DATE<=CONCAT(LEFT(CURRENT_DATE,'8'),'24')

执行结果

执行上面的sql,结果如下:

ID  ORDER_ID  PRODUCT_ID  CREATE_DATE  CREATE_TIMESTAMP
 
2  1  1  2018-03-25  2018-03-25 23:33:33
 
3  1  1  2018-03-26  2018-03-26 23:33:33
 
4  1  1  2018-03-27  2018-03-27 23:33:33
 
5  1  1  2018-04-21  2018-04-21 23:33:33
 
6  1  1  2018-04-23  2018-04-23 23:33:33
 
7  1  1  2018-04-24  2018-04-24 23:33:33

执行sql的时间是4月23号晚上,对应的日期区间是3月25号到4月24号.可以看到3月25号之前和4月24号之后的数据都被过滤掉了。

本文由职坐标整理并发布，了解更多内容，请关注职坐标数据库MySQL频道！