踩坑实录：MySQL 子查询单独有结果，套外层就为空？元凶找到了

问题现象业务查询车辆申请单时遇到诡异问题子查询单独执行正常返回多条数据外层套一层SELECT * FROM (子查询) c结果直接为空字段、条件、JSON 解析均无语法错误加update_time到 SELECT 也无效问题 SQL-- 外层查询结果为空 select * from ( SELECT id, update_time FROM t_vehicle_apply_order WHERE delete_flag 0 AND apply_date BETWEEN 2026-03-27 AND 2026-03-30 AND ( create_user_id 940245941670387712 OR EXISTS ( SELECT 1 FROM JSON_TABLE( approve_info, $[*] COLUMNS ( roleName VARCHAR(50) PATH $.roleName, approveUserId VARCHAR(50) PATH $.approveUserId ) ) AS jt WHERE jt.roleName confirmer AND jt.approveUserId 1000000640 ) ) ORDER BY update_time DESC ) c;根本原因这是MySQL 5.7/8.0 官方优化规则不是 Bug标准 SQL 规定子查询派生表的ORDER BY不保证生效仅用于配合LIMIT筛选前 N 条MySQL 优化器行为派生表中ORDER BY无 LIMIT→ 判定排序无意义 → 直接忽略排序逻辑极端场景下会导致结果集被优化为空触发条件子查询含ORDER BY、无LIMIT、作为FROM派生表嵌套简单说只排序不限条数MySQL 认为没必要做直接把结果 “优化没了”。解决方案二选一方案 1子查询加超大 LIMIT最快修复给子查询加一个足够大的LIMIT让优化器保留排序与结果select * from ( SELECT id FROM t_vehicle_apply_order WHERE delete_flag 0 AND apply_date BETWEEN 2026-03-27 AND 2026-03-30 AND ( create_user_id 940245941670387712 OR EXISTS ( SELECT 1 FROM JSON_TABLE(approve_info, $[*] COLUMNS ( roleName VARCHAR(50) PATH $.roleName, approveUserId VARCHAR(50) PATH $.approveUserId )) AS jt WHERE jt.roleName confirmer AND jt.approveUserId 1000000640 ) ) ORDER BY update_time DESC LIMIT 99999999 -- 关键修复 ) c;方案 2移到外层排序规范写法推荐删除子查询ORDER BY在外层执行排序语义更清晰SELECT id, update_time FROM ( SELECT id, update_time FROM t_vehicle_apply_order WHERE delete_flag 0 AND apply_date BETWEEN 2026-03-27 AND 2026-03-30 AND ( create_user_id 940245941670387712 OR EXISTS ( SELECT 1 FROM JSON_TABLE(approve_info, $[*] COLUMNS ( roleName VARCHAR(50) PATH $.roleName, approveUserId VARCHAR(50) PATH $.approveUserId )) AS jt WHERE jt.roleName confirmer AND jt.approveUserId 1000000640 ) ) ) c ORDER BY update_time DESC;核心知识点总结子查询排序生效前提必须配合LIMIT/OFFSET否则 MySQL 会忽略派生表排序无效FROM (SELECT ... ORDER BY)无 LIMIT排序不生效且可能丢结果最佳实践排序逻辑统一放在最外层查询避免嵌套排序踩坑兼容提示该问题仅 MySQL 特有Oracle、PostgreSQL 无此优化行为避坑指南嵌套查询尽量不要在子查询写ORDER BY必须排序时子查询加LIMIT 99999999兜底复杂 JSON 查询 排序场景优先外层排序减少优化器干扰