Mysqli带OR条件的日期查询返回异常数据问题求助

需求概述 #

从Schedule表中查询满足以下条件的id：

• 日期为2022-09-03
• kickoff时间在17:45到18:55之间
• center、ar1、ar2、4th、mentor任一字段值为'96'

问题根源 #

1. 初始SQL的运算符优先级错误 #

初始SQL语句：

SELECT id FROM Schedule WHERE center = '96' OR ar1 = '96' OR ar2 = '96' OR 4th = '96' OR mentor = '96' AND date = '2022-09-03' AND kickoff <= '17:45' AND kickoff >= '18:55'

SQL中AND的优先级高于OR，这条语句的实际逻辑等价于：

SELECT id FROM Schedule 
WHERE (center = '96' OR ar1 = '96' OR ar2 = '96' OR 4th = '96') 
OR (mentor = '96' AND date = '2022-09-03' AND kickoff <= '17:45' AND kickoff >= '18:55')

这会导致只要center/ar1/ar2/4th任意一个为'96'，不管日期和时间是否符合条件，都会被查询出来，这就是2022-09-11的AR1字段为'96'的数据被错误返回的原因。

2. 修改后SQL的时间范围条件写反 #

修改后的SQL虽然给OR条件加了括号，但kickoff的范围判断完全错误：

kickoff <= '17:45' AND kickoff >= '18:55'

没有任何时间能同时满足"小于等于17:45"且"大于等于18:55"，这部分条件永远为FALSE，导致查询结果要么为空，要么完全不符合预期。

正确SQL语句 #

写法一：明确括号与正确的时间范围 #

SELECT id FROM Schedule 
WHERE (center = '96' OR ar1 = '96' OR ar2 = '96' OR `4th` = '96' OR mentor = '96') 
AND date = '2022-09-03' 
AND kickoff >= '17:45' AND kickoff <= '18:55';

注意：4th是数字开头的特殊字段名，需用反引号`包裹避免语法错误（部分数据库如MySQL要求，其他数据库可根据规则调整）。

写法二：用BETWEEN简化时间范围判断 #

SELECT id FROM Schedule 
WHERE (center = '96' OR ar1 = '96' OR ar2 = '96' OR `4th` = '96' OR mentor = '96') 
AND date = '2022-09-03' 
AND kickoff BETWEEN '17:45' AND '18:55';

BETWEEN会自动处理"大于等于左值且小于等于右值"的逻辑，代码更简洁。

内容的提问来源于stack exchange，提问作者orddie