PDO执行带用户变量的嵌套SQL时外层排序失效如何解决？

1. 核心是服务端MariaDB 10.1和本地MySQL 8.0的查询优化策略差异：MariaDB 10.1对包含用户自定义变量的派生表（即$query中的嵌套子查询）做优化时，会触发派生表合并逻辑，该逻辑会错误忽略外层拼接的ORDER BY date desc条件，或者在合并过程中打乱结果顺序，最终表现为排序失效。
2. PDO请求场景下的执行计划差异：直接在数据库执行SQL时，优化器会选择先物化派生表再排序的正确执行路径；而通过PHP/PDO发起查询时，预编译、会话参数等因素会让优化器选择错误的执行计划，触发有问题的派生表合并逻辑，导致排序失效。

你排查到的两个现象也可以对应这个逻辑：

• 删除$query外层的select * from后没有了派生表，不需要走合并逻辑，外层排序直接生效
• 删除用户变量后，派生表合并逻辑可以正常处理排序规则，不会出现顺序混乱

不需要修改外层拼接逻辑，只要调整$query，在内层子查询中加入一个超大的LIMIT值，强制优化器跳过派生表合并，先物化内层查询结果再执行外层排序即可，调整后的$query代码如下：

select * from
(
    select
        id_field as 'id', 
        date_field as 'date', 
        name_field as 'name', 
        cast((@counter := @counter + 1) AS SIGNED) as 'counter'
    from my_table
    cross join (select @counter := 0) as tmp
    LIMIT 18446744073709551615
) as x

其中18446744073709551615是MySQL/MariaDB支持的最大无符号整数，加入后不会过滤任何数据，只会阻止优化器执行派生表合并，完美兼容你保留counter字段、不修改外层拼接逻辑的需求。

内容的提问来源于stack exchange，提问作者Matías Cánepa