MariaDB datetime毫秒匹配异常：带ORDER BY与不带返回结果不同

问题背景 #

• DB版本：10.5.13-MariaDB-log
• 表结构：存在表sample_table，包含create_at(datetime)、another_column(varchar)列，已有多条数据，其中一条create_at值为2022-07-29 07:19:09.0。

执行的查询及结果 #

查询1：

SELECT *
FROM sample_table
WHERE create_at = '2022-07-29 07:19:09.1234'

返回1行结果

查询2：

SELECT *
FROM sample_table
WHERE create_at = '2022-07-29 07:19:09.1234'
ORDER BY another_column;

返回0行结果

核心原因 #

这是由于数据类型精度限制和执行计划差异导致的：

1. datetime类型精度限制
   MariaDB中默认的datetime类型仅支持秒级精度（小数点后0位），无法存储毫秒值。表中那条数据的create_at实际存储的是2022-07-29 07:19:09，末尾的.0只是显示格式，并非实际存储的毫秒部分。

2. 执行计划的差异导致匹配逻辑不同

   • 查询1大概率使用了仅包含create_at的覆盖索引。此时数据库在索引层匹配时，会自动将查询条件中的毫秒值截断为秒级（即2022-07-29 07:19:09），与索引中存储的秒级值匹配，因此返回了那行数据。
   • 查询2因为添加了ORDER BY another_column，无法再使用仅包含create_at的覆盖索引——必须回表读取完整数据行，获取another_column的值用于排序。此时数据库会对读取到的create_at原始值与带毫秒的查询条件做精确对比，而原始值没有毫秒部分，两者不相等，所以返回0行。

验证与修复方案 #

• 验证方式：执行EXPLAIN查看两个查询的执行计划，能明显看到查询1走索引，查询2需要回表或全表扫描。
• 修复方案：
  • 若需要支持毫秒级时间存储，将create_at列修改为datetime(6)类型（支持微秒级精度），并重新插入或更新数据以保留毫秒值。
  • 查询时统一精度，要么将查询条件截断为秒级：WHERE create_at = '2022-07-29 07:19:09'；要么使用函数对齐精度后匹配：WHERE DATE_FORMAT(create_at, '%Y-%m-%d %H:%i:%s') = DATE_FORMAT('2022-07-29 07:19:09.1234', '%Y-%m-%d %H:%i:%s')（注意函数会导致索引失效，若需性能建议优先修改列类型）。

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