mysql select between two dates 有奇怪的行为

Question

我正在选择 NOW() 和特定 X 天间隔之间的所有记录，遇到了这种我不理解的奇怪行为。

我正在检查 future 24 小时和过去 ​​24 小时:

select * from table where date between NOW() and NOW() + 1 interval day; //works
select * from table where date between NOW() and NOW() - 1 interval day; //no records

但是如果我反转 between 调用:

select * from table where date between NOW() + 1 interval day AND NOW(); //no records
select * from table where date between NOW() - 1 interval day AND NOW(); //works

为什么对 future 的调用有效，但对过去的调用无效？...如果我反转 between 参数，则会发生相反的行为 - 24 小时后不起作用 future ，但确实在过去 24 小时内工作。

======================

在下面写出@TimBiegeleisen 的解释:

date = '2018-05-30' ;

select * from table where date between NOW() and NOW() + 1 interval day;
     = date >= '2018-05-30' AND 'date <= 2018-05-31'; //true
select * from table where date between NOW() and NOW() - 1 interval day; records
     = date >= '2018-05-30' AND 'date <= 2018-05-29'; //false

和

select * from table where date between NOW() + 1 interval day AND NOW();
     = date >= '2018-05-31' AND date <= '2018-05-30' //false
select * from table where date between NOW() - 1 interval day AND NOW();
     = date >= '2018-05-29' and date <= '2018-05-30'; //true

Answer 1

BETWEEN 运算符以某种方式解释:

WHERE date BETWEEN a AND b

意思是:

WHERE date >= a AND date <= b

所以下面两个查询是等价的:

select * from table where date between NOW() and NOW() - interval 1 day;
select * from table where date >= NOW() and date <= NOW() - interval 1 day;

希望您能在第二个查询中看到 WHERE 条件永远不会为真，因为日期不能同时大于或等于现在和小于现在同时减一。