Mysqli时间区间查询在PHP中返回无效结果问题排查

问题详情 #

• 数据表pokemons_location包含timezone、timezone_b两个TIME类型字段，用于存储记录的有效时间区间
• 用PHP mysqli编写的预处理查询，逻辑是筛选指定地点、符合概率条件，且当前时间在设定区间内的记录
• 这套逻辑在其他站点长期正常，但当前环境出现异常：比如执行时系统时间为23:08:24，却返回了本该仅在23:55:00-04:00:00出现的条目，且仅在每小时前15-20分钟触发该错误
• 直接在MySQL客户端使用相同参数执行查询，结果正确；服务器时间、MySQL的CURTIME()均正常，NTP同步偏差仅0.001042秒

原PHP代码 #

$stmts = Work::$sql->prepare('SELECT
                                *
                                 FROM `pokemons_location`
                                 WHERE
                                 `location_id` = ? AND
                                 `chance` >= ? AND
                                 (
                                 (`timezone` = ? AND `timezone_b` = ?)
                                   OR
                                 (CAST(? AS time) BETWEEN timezone AND timezone_b)
                                    OR (NOT CAST(? AS time) BETWEEN timezone_b 
                                 AND timezone AND timezone > timezone_b)
                                 )

                                    ORDER BY `chance` DESC
                                    LIMIT ?
                                    ');
$zero = "00:00:00";
$chance = mt_rand(1, 100);
$timezone = date('H:i:s');        
$chance_limit = random_int(5, 15);

$stmts->bind_param("iisssss", $location_id, $chance, $zero,$zero, $timezone, $timezone, $chance_limit);
$stmts->execute();
$select = $stmts->get_result();

问题根源 #

1. 参数绑定类型不匹配：你将PHP生成的时间字符串用s（字符串类型）绑定，但MySQL的TIME类型在预处理阶段的隐式字符串转换，在特定环境下可能出现解析偏差——尤其是每小时前15分钟左右的时间格式，容易触发边缘解析错误
2. 跨天时间逻辑冗余易出错：原查询中NOT CAST(...) BETWEEN ... AND timezone > timezone_b的写法逻辑绕，很容易因为类型转换问题导致判断条件失效

修复方案 #

方案1：用MySQL原生函数获取当前时间（推荐） #

直接在SQL中使用CURTIME()获取当前时间，彻底避免PHP与MySQL之间的时间参数传递及类型转换问题：

$stmts = Work::$sql->prepare('SELECT
                                *
                                 FROM `pokemons_location`
                                 WHERE
                                 `location_id` = ? AND
                                 `chance` >= ? AND
                                 (
                                 -- 全天可出现的情况
                                 (`timezone` = ? AND `timezone_b` = ?)
                                   OR
                                 -- 非跨天区间：当前时间在区间内
                                 (CURTIME() BETWEEN `timezone` AND `timezone_b`)
                                    OR
                                 -- 跨天区间：当前时间大于起始时间，或者小于结束时间
                                 (`timezone` > `timezone_b` AND (CURTIME() >= `timezone` OR CURTIME() <= `timezone_b`))
                                 )
                                    ORDER BY `chance` DESC
                                    LIMIT ?
                                    ');
$zero = "00:00:00";
$chance = mt_rand(1, 100);
$chance_limit = random_int(5, 15);

// 参数绑定类型更明确：int、int、string、string、int
$stmts->bind_param("iissi", $location_id, $chance, $zero, $zero, $chance_limit);
$stmts->execute();
$select = $stmts->get_result();

方案2：若必须从PHP传时间，修正绑定类型 #

如果业务需要必须从PHP传递时间参数，将绑定类型中的s替换为t（PHP 7.0+支持mysqli绑定TIME类型）：

// 修改绑定参数类型为iitttti
$stmts->bind_param("iitttti", $location_id, $chance, $zero,$zero, $timezone, $timezone, $chance_limit);

额外检查点 #

• 确认表中timezone和timezone_b字段确实为TIME类型，而非VARCHAR等字符串类型
• 执行查询前可通过var_dump($timezone)输出PHP生成的时间字符串，确认格式为标准的HH:MM:SS，无多余字符

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