1. “查询一整天”这个需求为什么在 MyBatis 里总翻车1.1 一个五分钟需求背后的数据类型错位先说个我闭着眼都能背出来的场景运营后台有个订单列表产品提的需求是“按日期筛选选 12 月 1 日就出 12 月 1 日这一天的所有订单”。前端日期组件返回的是2024-12-01这个字符串后端查询对象里顺手就定义成了String startDate数据库里订单创建时间create_time是datetime类型MyBatis 负责把结果映射回 VO。看起来确实五分钟能写完先where create_time #{startDate}不行就换BETWEEN再不行就上LIKE。等你真把 SQL 塞进数据库跑一遍就会发现这五分钟写出来的东西十条订单能漏掉九条半而且不是稳定漏——有时候能查到几条有时候一条都没有最气人的是开发环境数据少自测时随手查一个日期碰巧能过上了生产才爆雷。问题根源不在需求而在“字符串日期”和“datetime 字段”这两个东西之间的错位。datetime在 MySQL 里是一个精确到秒或毫秒、微秒取决于列定义的时间点2024-12-01 08:30:00、2024-12-01 23:59:59都是合法值但2024-12-01不是一个合法的时间点它只是一个“日期”。把日期字符串丢给 datetime 字段去比较MySQL 一定会做点什么来弥合这个差异而你大概率没意识到的就是它到底做了怎样的转换。1.2 隐式转换等值查询只匹配到零点先说结论当你在 SQL 里把2024-12-01这个字符串和 datetime 字段做等值比较时MySQL 会做隐式转换把字符串解释成2024-12-01 00:00:00。换句话说WHERE create_time 2024-12-01在语义上等价于WHERE create_time 2024-12-01 00:00:00它只能匹配当天零点整那一秒钟的数据。而你的真实需求是“一整天”也就是从00:00:00到23:59:59如果列是datetime(3)或datetime(6)还要覆盖到毫秒和微秒。需求和 SQL 从一开始就是两个不同的集合查不全才是常态。这种问题在新项目里特别容易发生因为 MyBatis 的#{}参数帮你把引号、类型这些都处理“干净”了你很容易忽略 MySQL 内部对这个字符串到底做了什么事。数据量小的时候偶尔选中一条零点附近的订单看起来还“挺正常”侥幸感一上来坑就留到上线了。1.3 三层复杂度缺一层都容易出问题这个主题表面上是个“日期查询的小问题”真正拆开其实有三层SQL 语义层字符串怎么转换成 datetime范围边界怎么划BETWEEN、、、LIKE各有什么后果MyBatis 实现层XML 里用#{}还是${}if判断空字符串动态 SQL 的语法参数怎么传才不会在边界场景翻车运行性能层你写出的条件能不能走索引函数是作用在字段上还是作用在参数上一张几百万行的表会不会被你一个看似无害的条件打成全表扫描。下面我按这三层把这几年的项目里攒下的经验和踩坑复盘完整展开。2. 最容易踩进去的四种错误写法2.1 BETWEEN 与“当天日期”微弱正确感掩盖的大漏select idselectByCreateTime resultTypeOrderDO SELECT * FROM t_order WHERE create_time BETWEEN #{startDate} AND #{endDate} /selectService 层传入startDate 2024-12-01、endDate 2024-12-01。表面上一看BETWEEN 是闭区间包含等于逻辑没毛病。但结合前面说的隐式转换这个条件等价于WHERE create_time 2024-12-01 00:00:00 AND create_time 2024-12-01 00:00:00注意这个范围里只有一个点就是零点整。08:30 的订单不在里面下午三点的订单不在里面晚上十一点的订单更不在里面。实际表现就是能查出几条刚好半夜下的单白天所有单全部消失或者因为索引选择性问题MySQL 干脆全表扫扫完给你一个差不多的结果。这种错误最大的危害是它有“微弱的正确感”数据能出来只是不全很容易被误判成数据同步延迟、订单没进来而不是 SQL 写错了。我之前在代码评审里看到过有人加了个注释“endDate 传当天日期BETWEEN 查整天”这就是典型的认知错误。顺带提一个细节有人会用endDate 2024-12-01 23:59:59来补救。秒级字段下这确实能覆盖一整天但如果字段是datetime(3)或datetime(6)23:59:59.500这条数据就又漏掉了。用23:59:59.999这种字符串看起来是“补满了”实际遇到精度更高的列或某些数据库的舍入规则依然有边界风险。锁死“当天某个最晚时间字符串”的做法本质上是赌精度不适合作为通用方案。2.2 单条件 少一天且不易察觉WHERE create_time lt; #{endDate}这种写法经常出现在“统计 12 月 1 日及之前的累计数据”这类需求里。传一个endDate 2024-12-01经过隐式转换变成 2024-12-01 00:00:00那么 12 月 1 日白天的订单又没了。比起 BETWEEN这个错误更隐蔽因为它是单条件出错后你很难第一时间怀疑到它头上。我的经验是凡是看到datetime 字段 纯日期字符串第一反应就应该是“少了整整一天”不用犹豫直接改成左闭右开区间。2.3 LIKE 模糊匹配功能对了性能和语义崩了WHERE create_time LIKE 2024-12-01%这条 SQL 在功能上的确能覆盖一整天因为2024-12-01%能以字符串前缀的形式匹配2024-12-01 08:00:00、2024-12-01 23:59:59。问题出在性能和语义上性能方面LIKE 2024-12-01%因为前缀固定理论上还有机会走索引但前提是列类型是字符串如果列是 datetimeMySQL 会先把 datetime 转成字符串再比较这等于对每一行做一次格式化然后逐个匹配前缀大表上查询时间从毫秒涨到秒级很常见。更糟的写法是LIKE %2024-12-01%前缀用通配符索引必然失效全表扫描跑不掉。语义方面这种写法把“时间范围查询”变成了“字符串模式匹配”将来字段类型或格式一变行为就跟着变维护成本很高。我在一个老项目里接手过类似的慢查询单表七百万行运营每次拉月报数据库 CPU 直接飙到 90%DBA 追过来一看就是LIKE 2024-12%这种。改成区间条件之后查询从 8 秒降到 20 毫秒索引完全吃上。这里的关键不是“能不能查到”而是“能不能稳定、高效地查到”。2.4 Java 层拼 23:59:59用表象逻辑掩盖精度问题String start 2024-12-01 00:00:00; String end 2024-12-01 23:59:59;老项目里这种写法不少把边界问题从 SQL 挪到 Java看起来是“两头都想到了”。问题是这是典型的“面向表象编程”今天字段是秒级没问题哪天字段升级成datetime(6)23:59:59.999的数据依旧漏哪天产品说“我要查 12 月 1 日 0 点到 12 月 2 日 0 点”你的字符串拼接又要改。更尴尬的是Java 层拼好了字符串MyBatis 里还是#{}传参问题绕了一圈最后还是落在字符串和 datetime 的隐式转换里打转。3. 根上的解法左闭右开区间 显式日期转换3.1 为什么“小于次日零点”是最稳的边界关于日期范围查询行业里逐渐形成的共识是左闭右开区间条件写成create_time 开始日期的 00:00:00且create_time 结束日期1 天的 00:00:00。查 12 月 1 日一整天就是WHERE create_time 2024-12-01 00:00:00 AND create_time 2024-12-02 00:00:00为什么是“小于次日零点”而不是“小于当天 23:59:59”因为“当天最后一刻”不是一个可以被精确表达的单点秒级是 23:59:59毫秒级是 23:59:59.999微秒级是 23:59:59.999999。不管你写哪一个都存在比它更晚、但仍属于“当天”的数据。而“次日零点”是一个精确、无歧义的点任何属于当天的数据都严格小于它。把 end 定义为第二天的开始边界问题就被彻底消解了。这个思路其实和数组切片、价格区间是同一套逻辑[0, length)永远比[0, lastIndex]健壮因为你不需要脑补最后一个元素的位置边界天然由下一个起点划定。3.2 STR_TO_DATE 显式转换字符串日期当参数是字符串2024-12-01最稳妥的做法是不要依赖隐式转换而是显式用STR_TO_DATE把它转成 datetimeSELECT * FROM t_order WHERE create_time STR_TO_DATE(2024-12-01, %Y-%m-%d) AND create_time STR_TO_DATE(2024-12-01, %Y-%m-%d) INTERVAL 1 DAY;STR_TO_DATE(2024-12-01, %Y-%m-%d)返回2024-12-01 00:00:00加INTERVAL 1 DAY后变成2024-12-02 00:00:00两个条件刚好圈出 12 月 1 日的一整天。为什么不直接写create_time 2024-12-01直接写也能跑但显式转型有三个好处可读性好后来的人一看就知道你是想把字符串转成当天零点而不是在那里碰运气避免隐式转换的规则差异不同 MySQL 版本、不同sql_mode比如NO_ZERO_DATE下隐式转换行为可能不一样函数作用在参数上而不作用在字段上不会影响 create_time 字段的索引使用这一点在第 5 部分会重点展开。3.3 DATE() 字段函数写法以及为什么不能推广还有一种常见写法WHERE DATE(create_time) 2024-12-01语义直观一眼就懂“把这天的数据拿出来”。我认可它在小表、低频后台查询里可以用。但请注意DATE(create_time)是对字段做函数运算函数一旦套上去MySQL 基本无法在 create_time 索引上做范围扫描。它需要先把每一行的 create_time 取出来算一遍 DATE()再和字符串比较。表一大这条 SQL 就开始拖后腿。MySQL 8.0 里可以通过生成列加表达式索引的方式让DATE(create_time)命中二级索引这是另一个进阶话题。普通项目不建议为了一个查询条件去动表结构通用做法仍然是把函数写在参数侧保持条件 Sargable可索引。3.4 四种写法放在一起看写法能否覆盖一整天索引友好可读性我的判断create_time #{date}否只匹配零点是高不推荐DATE(create_time) #{date}是否高小表可用DATE_FORMAT(create_time, %Y-%m-%d) #{date}是否中不推荐create_time STR_TO_DATE(#{date}, %Y-%m-%d) AND create_time STR_TO_DATE(#{date}, %Y-%m-%d) INTERVAL 1 DAY是是高推荐DATE_FORMAT那行我直接标不推荐它把 datetime 转成字符串之后再做字符串比较精度丢失和全表扫描双重叠加比DATE()更糟。4. MyBatis 中的落地方案从 XML 到 Service 层4.1 Mapper 接口、查询对象与核心 XML 代码先定义查询对象日期字段保持字符串Data public class OrderQuery { /** 开始日期格式 yyyy-MM-dd例如 2024-12-01包含当天 */ private String startDate; /** 结束日期格式 yyyy-MM-dd例如 2024-12-31包含当天 */ private String endDate; private Integer status; }Mapper 接口public interface OrderMapper { ListOrderDO selectByDateRange(Param(query) OrderQuery query); }XML 里的核心写法select idselectByDateRange resultTypecom.example.OrderDO SELECT id, order_no, create_time, status FROM t_order where if testquery.startDate ! null and query.startDate ! AND create_time gt; STR_TO_DATE(#{query.startDate}, %Y-%m-%d) /if if testquery.endDate ! null and query.endDate ! AND create_time lt; STR_TO_DATE(#{query.endDate}, %Y-%m-%d) INTERVAL 1 DAY /if if testquery.status ! null AND status #{query.status} /if /where ORDER BY create_time DESC /select这里有几个细节值得注意。where标签会自动去掉第一个条件的AND避免生成WHERE AND这种语法错误如果所有条件都为空它会生成一个没有 WHERE 的语句这在全表查询场景下是合理的默认行为。if test判断字符串空值是必须的。很多新手只写! null结果前端传了空字符串SQL 变成create_time STR_TO_DATE(, %Y-%m-%d)MySQL 对空字符串做日期转换会返回 NULL 或者直接报Incorrect datetime value整个查询直接挂掉。这个问题在 MyBatis 里出现频率非常高。XML 中大于号、小于号必须转义可以用gt;必须写成lt;。一旦漏了转义XML 解析阶段直接报错根本轮不到数据库。上面代码里两个比较符号我都做了转义这是很基本的规范但 review 代码时能看到不少漏网之鱼。4.2 动态 SQL 中空值判断与 XML 转义事项补充一个和日期无关但经常一起踩的坑如果你把参数直接写在if的判断条件里注意用query.xxx这种 OGNL 表达式的路径。之前遇到过有人写成if teststartDate ! null and startDate ! 而实际参数是Param(query)正确写法应该是query.startDate。写错路径后MyBatis 在解析阶段不会立刻报错运行时才报There is no getter for property named startDate排查时容易被我忽略。另外if判断里不要把字符串比较写反比如 ! query.startDateOGNL 对这种写法的解析结果有时候和直觉不一致。统一写成query.startDate ! null and query.startDate ! 最稳。4.3 Service 层日期字符串的归一化处理前端传来的日期不一定规整。日期组件可能传2024/12/01运营贴进 Excel 的数据可能变成2024-12-1还有人会传2024.12.01。这些字符串直接进STR_TO_DATE靠%Y-%m-%d格式去猜能不能解析就看数据库的容错度了。稳妥的做法是在 Service 层统一归一到yyyy-MM-ddpublic ListOrderDO queryOrders(OrderQuery query) { if (StringUtils.hasText(query.getStartDate())) { query.setStartDate(normalizeDate(query.getStartDate())); } if (StringUtils.hasText(query.getEndDate())) { query.setEndDate(normalizeDate(query.getEndDate())); } return orderMapper.selectByDateRange(query); } private String normalizeDate(String raw) { String normalized raw.trim() .replaceAll([/.], -); LocalDate date LocalDate.parse(normalized, DateTimeFormatter.ofPattern(yyyy-M-d)); return date.format(DateTimeFormatter.ofPattern(yyyy-MM-dd)); }DateTimeFormatter.ofPattern(yyyy-M-d)能同时解析2024-1-5和2024-01-05格式化回去统一成2024-01-05。这样传到 XML 里的一定是干净的yyyy-MM-ddSTR_TO_DATE基本不会踩解析坑。有的团队会把这个约束推给前端要求前端保证格式。可以但后端做一次归一化成本很低收益是出问题时不用扯皮我建议后端还是自己堵住这一层。4.4 在 XML 里转还是在 Service 层转两种写法的取舍前面 4.1 的方案是在 XML 里用STR_TO_DATE和INTERVAL 1 DAY把字符串边界计算交给数据库。另一种做法是在 Service 层直接把日期算成LocalDateTimeXML 只保留最朴素的和public ListOrderDO queryOrders(OrderQuery query) { if (StringUtils.hasText(query.getStartDate())) { LocalDate start LocalDate.parse(query.getStartDate()); query.setStartDateTime(start.atStartOfDay()); // 2024-12-01T00:00 } if (StringUtils.hasText(query.getEndDate())) { LocalDate end LocalDate.parse(query.getEndDate()); query.setEndDateTime(end.plusDays(1).atStartOfDay()); // 2024-12-02T00:00 } return orderMapper.selectByDateRange(query); }DTO 里加两个LocalDateTime字段XML 变成if testquery.startDateTime ! null AND create_time gt; #{query.startDateTime} /if if testquery.endDateTime ! null AND create_time lt; #{query.endDateTime} /if这条方案的好处是 XML 更干净SQL 连字符串格式都不用关心MyBatis 的 TypeHandler 会把LocalDateTime转成 JDBC 的 TIMESTAMP比较语义清晰坏处是 DTO 里不能只放朴素的字符串要多维护两个时间字段而且“1 天”的逻辑写在了 Java 里DBA 排查慢 SQL 时要多跨一层。两条方案没有绝对优劣取决于团队规范。如果你们习惯“SQL 尽量朴素、业务计算放 Java”选 Service 层转换如果希望“日期边界逻辑都在 SQL 里DBA 拿着一条 SQL 就能复现问题”选 XML 转换。关键是选一种固化下来别一个项目里两种混着写。5. 从“查得到”到“查得快”索引、时区与跨数据库5.1 函数作用在字段上还是参数上决定索引的生死SQL 性能优化里有个术语叫 SargableSearch Argument Able核心规则一句话查询条件里函数作用在字段上通常会阻断索引函数作用在参数上不影响索引。拿推荐的写法对照-- 好函数作用在参数上字段裸露 create_time STR_TO_DATE(2024-12-01, %Y-%m-%d) -- 差函数作用在字段上索引失效 DATE(create_time) 2024-12-01第一条执行时MySQL 先把STR_TO_DATE(2024-12-01, %Y-%m-%d)算成常量2024-12-01 00:00:00然后对 create_time 做正常的范围扫描。只要 create_time 上有索引就能从索引定位到对应位置顺序读效率很高。第二条要把每一行的 create_time 先做一次 DATE() 运算再参与比较。即使 create_time 字段有索引也救不回来因为索引里存的是原始时间值不是 DATE() 的结果。这就是“一条条件两种命运”的典型例子。5.2 隐式转换的可预测性类型越乱执行计划越难猜项目里常见两类隐患一类是 datetime 字段和字符串直接比较靠隐式转换另一类是 VARCHAR 字段和 Date 参数比较又是另一套隐式规则。单看某一处数据库都能“猜”出个结果但一旦查询条件里混合了多种类型执行计划的可预测性就下降优化器可能选错索引甚至放弃索引。我的建议是datetime 字段就用日期类型或者在明确格式的字符串上显式STR_TO_DATE不要同一个 SQL 里一会儿STR_TO_DATE一会儿依赖隐式转换。线上出问题的时候这种“跑在隐式转换边缘”的 SQL 是最难排查的因为它不是稳定报错而是时好时坏。5.3 时区错位关于“日期少一天”的另一个源头假设业务库是 MySQL字段是 datetime应用跑在 Docker 容器里。镜像默认时区是 UTC宿主机是东八区数据库配置成东八区。这时如果你在 Service 层用new Date()或LocalDateTime.now()构造边界条件再通过#{startDateTime}传给 JDBC驱动会按 JVM 时区把时间转成 UTC 存进去再按数据库会话时区转回来。两边时区不一致就会出现“传了 12 月 1 日查出来的却是 11 月 30 日数据”的诡异现象。我自己就踩过这个坑当时应用查“昨天的数据”永远差 8 小时排查了半天才发现是容器时区问题。此类问题建议按顺序查三处JVM 默认时区、JDBC 连接 URL 里的serverTimezone参数、MySQL 的system_time_zone和会话time_zone。缓解办法数据库、应用、JDBC 连接参数三者统一时区URL 里显式写serverTimezoneAsia/ShanghaiDocker 里设置环境变量TZAsia/Shanghai并挂载/etc/localtime。这个问题排查优先级很高因为时区偏差会导致日期边界整体平移看起来像 SQL 写法错了实际是环境配置错了。5.4 不同数据库下同一语义的实现对照左闭右开的思路跨数据库通用但字符串转日期的函数各不相同数据库字符串转日期次日零点表达MySQLSTR_TO_DATE(2024-12-01, %Y-%m-%d) INTERVAL 1 DAYOracleTO_DATE(2024-12-01, YYYY-MM-DD) 1SQL ServerCONVERT(datetime, 2024-12-01, 120)DATEADD(day, 1, ...)PostgreSQL2024-12-01::date INTERVAL 1 dayOracle 的 1表示加一天因为 Oracle 的 DATE 类型默认带时间部分语义正好落在次日零点。SQL Server 的CONVERT第三个参数 120 表示yyyy-mm-dd hh:mi:ss样式也可以直接CAST(2024-12-01 AS datetime)。PostgreSQL 的::date是类型转换语法最直接。不管哪个数据库我最后给 DBA 看的 SQL 都是同一套语义WHERE create_time TO_DATE(2024-12-01, YYYY-MM-DD) AND create_time TO_DATE(2024-12-01, YYYY-MM-DD) 1人话翻译从 12 月 1 日零点开始到 12 月 2 日零点之前。这种边界不依赖当天最后一刻的 SQL换库也不容易出错。5.5 顺路复习 #{} 与 ${}日期参数也不能用拼接上面所有示例都用的是#{}占位符。如果有人图省事改成字符串拼接AND create_time STR_TO_DATE(${query.startDate}, %Y-%m-%d)startDate 直接进 SQL 文本一方面带来 SQL 注入风险用户传一个2024-12-01; DROP TABLE t_order;--进去后果不堪设想另一方面预编译失效每次执行都要重新解析 SQL性能也会受影响。MyBatis 面试里经常问#{}和${}的区别实际项目里这俩的选择对安全性的影响非常直接。日期参数一样永远用#{}别让用户输入直接进 SQL 文本。6. 一次“少查一天”线上问题的完整排查链路6.1 现象凌晨订单莫名消失有次线上反馈运营说“今天查昨天的订单凌晨那批数据没出来”。业务方第一反应是数据没落库让后端查表后端查了库凌晨订单在表里好好的但通过管理后台接口查不到。于是问题被定性为“查询接口 bug”。我接手时的第一件事不是改代码而是把接口最终执行的 SQL 从 MyBatis 日志里拉出来手动在数据库里跑一遍。这里说明一下MyBatis 的日志通常会打印预编译前的 SQL 和参数占位符需要把参数代进去还原出最终 SQL。还原后的条件长这样WHERE create_time 2024-12-01 00:00:00 AND create_time 2024-12-01 23:59:596.2 逐步定位从日志、执行计划到表结构第一步验证 SQL 本身。拿一个凌晨 00:30 的订单去套条件create_time 2024-12-01 23:59:59应该成立但查询结果确实没有。于是怀疑边界把条件改成WHERE create_time 2024-12-01 00:30:00能查到。这说明数据没问题、字段映射没问题问题缩小到时间范围表达式。第二步看表结构。执行SHOW CREATE TABLE t_order发现create_time的定义是datetime(6)精度到微秒。问题就清楚了凌晨 00:30:00.000000 的数据精度没问题但如果一条订单的时间是2024-12-01 00:30:00.123456它依然属于当天却大于23:59:59吗不是问题不在这。继续看日志里还原出的 SQL 中endDate 参数来自 Service 层拼的字符串2024-12-01 23:59:59通过 JDBC 传给 datetime(6) 字段比较时MySQL 会把这个字符串转成2024-12-01 23:59:59.000000。注意23:59:59.500000这种带小数秒的数据大于23:59:59.000000所以被漏掉了。看起来只差 1 微秒但确实不在范围内。第三步看执行计划。EXPLAIN显示走了全表扫描因为 LIKE 或边界函数导致了低选择性。这里更关键的结论是无论有没有索引这个查询的语义边界本身就是错的“补齐 23:59:59”这个方案在 datetime(6) 下天然有缝隙。6.3 修复与防复发把边界规则写进团队规范修复不复杂Service 层不再拼 “23:59:59”直接算次日零点XML 里改成AND create_time gt; #{startDateTime} AND create_time lt; #{endDateTime}其中endDateTime是LocalDate.parse(2024-12-01).plusDays(1).atStartOfDay()。真正难的是防复发。团队里不止一个人写过“拼 23:59:59”的代码我后来把日期范围查询的规范固化成了三条写进项目的开发手册时间范围统一用左闭右开区间结束时间总是“次日零点”日期字符串一律用yyyy-MM-dd转换用数据库的STR_TO_DATE/TO_DATE不依赖隐式转换条件里的函数只作用在参数上不对字段做任何包裹。这三条看着简单但能把一整类“日期边界”问题从根上解决。后来新人接手遇到日期查询就翻这三条很少再犯同样的错。6.4 一点个人经验日期查询的三个习惯最后分享几个我长期养成的习惯不算技术但很管用。第一写完日期条件先问自己一句“这个查询覆盖的区间边界是哪两个时间点”如果你答不出精确到秒的边界SQL 基本是有问题的。第二代码 Review 时看到“23:59:59”这个字符串我第一反应是提一个“改成左闭右开”的评论不用看上下文因为这个写法本身就带着精度风险的基因。第三查日期范围之前先看一眼表字段的类型和精度。datetime和datetime(6)对边界的要求完全不一样前者拼 23:59:59 顶多算丑后者会直接漏数据。把表结构看清楚再决定用哪种写法能省掉后面一整轮排查的时间。