MariaDB UPDATE语句仅添加ORDER BY时执行成功，报错Truncated incorrect DOUBLE value

遇到这种玄学问题确实头疼，尤其是从PostgreSQL转过来的同学，肯定更懵——毕竟PG在类型检查上可比MariaDB/MySQL严格多了😅

先给你拆解下核心问题：Truncated incorrect DOUBLE value这个错误，本质是数据库触发了失败的隐式类型转换——简单说就是它尝试把某个非数值类型的值转成DOUBLE类型时，碰到了没法转的内容（比如空字符串、非数字字符）。

那为什么SELECT正常，不加ORDER BY的UPDATE就报错呢？核心原因是SELECT和UPDATE的执行计划不一样，甚至加不加ORDER BY的UPDATE，执行计划也会有差异：

1. 执行计划差异导致的路径不同
   你的SELECT语句可能默认走了item_id的主键索引，直接精准定位到符合条件的行，全程没触发那些会导致转换错误的场景；但不加ORDER BY的UPDATE，优化器可能选了其他索引（比如你表上的item_ref_id索引，虽然你的WHERE里没用到subscription_id，但优化器有时候会“自作主张”选索引），在遍历表的过程中触发了隐式转换错误。
   而加了ORDER BY item_id ASC之后，相当于强制优化器按照主键的顺序来处理数据，被迫走了主键索引的扫描路径，刚好避开了之前有问题的执行逻辑，所以就正常了。

2. 结合你的场景，几个可能的具体诱因：

   • 隐性脏数据：虽然表定义里item_status是INT，但有没有可能历史上在宽松sql_mode下插入过非数字的内容？比如曾经用字符串类型插入，MySQL/MariaDB自动转成了INT，但留下了隐性的转换隐患？你可以跑个查询排查下：

     SELECT item_id, item_status FROM items WHERE item_status REGEXP '[^0-9]';
     

   • IN列表的隐性问题：如果你的IN列表是从某个字符串字段拼接来的，有没有可能混入了空字符串或者非数字字符？不过你说SELECT正常，这个可能性偏低，但可以试试把IN列表换成子查询（比如先把ID存到临时表，再用WHERE item_id IN (SELECT id FROM temp_table)），看看不加ORDER BY会不会报错。
   • 版本bug：某些旧版本的MariaDB/MySQL在处理带大IN列表的UPDATE时，当优化器选择特定执行计划（比如非主键索引扫描），会触发类型转换的bug。我之前在MySQL 5.7的某个小版本里碰到过类似情况，加ORDER BY强制改变执行计划就绕过了。

3. 给你几个验证方向：

   • 对比执行计划：分别跑这两条命令，看看索引选择和扫描方式的差异：

     EXPLAIN UPDATE items SET item_status = 15 WHERE item_id IN (<<list of ids>>) AND item_status = 7;
     EXPLAIN UPDATE items SET item_status = 15 WHERE item_id IN (<<list of ids>>) AND item_status = 7 ORDER BY item_id ASC;
     

   • 强制走主键索引：试试在UPDATE里加FORCE INDEX (PRIMARY)，看看不加ORDER BY能不能正常执行：

     UPDATE items FORCE INDEX (PRIMARY)
     SET item_status = 15
     WHERE item_id IN (<<list of ids>>) AND item_status = 7;
     

   • 检查sql_mode：看看是不是开启了宽松模式（比如sql_mode里没有STRICT_TRANS_TABLES），宽松模式下允许的隐式转换可能在某些操作下触发意外错误。

总的来说，这个问题就是执行计划差异导致的隐式转换bug/异常，加ORDER BY只是绕过了问题而已。如果不想花太多时间深究，用这个“丑陋的解决方案”完全没问题；要是想彻底解决，就顺着执行计划和版本bug的方向排查就行。

备注：内容来源于stack exchange，提问作者broom