mysql中between的边界,范围说明

tangjin

作者

mysql between的边界范围

between 的范围是包含两边的边界值

eg： id between 3 and 7 等价与 id >=3 and id<=7

not between 的范围是不包含边界值

eg：id not between 3 and 7 等价与 id < 3 or id>7

SELECT * FROM `test` where id BETWEEN 3 and 7; 等价于 SELECT * FROM `test` where id>=3 and id<=7; ----------------------------------------------------------- SELECT * FROM `test` where id NOT BETWEEN 3 and 7; 等价于 SELECT * FROM `test` where id<3 or id>7;

mysql between日期边界的问题留意

边界问题：

mysql， between 开始日期 AND 结束日期包含开始日期，不包含结束日期

例如：

BETWEEN '2018-01-22' AND '2018-01-30'

开始日期从2018-01-22 00:00:00.0 开始，到2018-01-29 23:59:59.59结束

表中的CREATE_DATE 是varchar(21) DEFAULT NULL COMMENT '时间',

CREATE_DATE中保存值是：年-月-日时：分：秒：0 例如： 2018-01-29 23:45:35.0

SELECT * FROM Test a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30' ORDER BY a.CREATE_DATE desc

SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-30' ORDER BY a.CREATE_DATE desc 2018-01-29 23:45:35.0 20180129 2018-01-29 23:45:33.0 20180129 2018-01-29 00:10:58.0 20180129 2018-01-29 00:10:45.0 20180129 2018-01-28 23:42:23.0 20180128 2018-01-28 23:39:39.0 20180128 SELECT * FROM TABEL a WHERE a.CREATE_DATE BETWEEN '2018-01-22' AND '2018-01-29' ORDER BY a.CREATE_DATE desc 2018-01-28 23:42:23.0 20180128 2018-01-28 23:39:39.0 20180128 2018-01-28 00:13:22.0 20180128 2018-01-28 00:13:19.0 20180128 2018-01-27 23:23:02.0 20180127 2018-01-22 00:09:59.0 20180122 2018-01-22 00:09:56.0 20180122 2018-01-22 00:01:53.0 20180122

遇到的其他问题：

遇到另外一张表 test2 有保存时间的字段： `REPORTTIME` varchar(45) DEFAULT NULL,

这个字段保存的值是：

例子1：

select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d') BETWEEN '2018-01-16' AND '2018-01-27' ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC ;

结果1：

从结果中，可以看到取到了27号的数据，可能是处理的时间没有小时，分钟，秒。

例子2：

select * from bips_hpd_helpdesk a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d') BETWEEN str_to_date('2018-01-16','%Y-%m-%d') AND str_to_date('2018-01-27','%Y-%m-%d')

结果2：

找到问题：毫秒值转换为时间，发现这里保存的毫秒值，没有保存时分秒：

from_unixtime(a.REPORTTIME,'%Y-%m-%d') AS reportTime,a.REPORTTIME, str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s') AS reportTime22 FROM test a WHERE str_to_date(from_unixtime(a.REPORTTIME,'%Y-%m-%d'),'%Y-%m-%d %h:%i:%s') BETWEEN str_to_date('2018-01-16','%Y-%m-%d %h:%i:%s') AND str_to_date('2018-01-27 %h:%i:%s','%Y-%m-%d') #subdate(curdate(),date_format(curdate(),'%w')-1) AND subdate(curdate(),date_format(curdate(),'%w')-8) ORDER BY from_unixtime(a.REPORTTIME,'%Y-%m-%d') DESC ;

查看到的时间值：