同版本MySQL 5.6查询执行异常，求排查遗漏配置项

这个问题的核心在于MySQL查询优化器对派生表的处理逻辑差异，虽然都是5.6版本，但不同环境的优化器配置可能打乱了你依赖的用户变量执行顺序。

最可能的罪魁祸首：optimizer_switch中的derived_merge参数 #

MySQL 5.6默认开启了derived_merge优化，它会尝试将嵌套的派生表合并到外层查询中，以提升性能。但这种合并会破坏你查询中依赖的排序后变量赋值顺序——你的逻辑需要先按name和position排序，再依次给@rank和@prev赋值，一旦优化器合并了派生表，这个顺序就可能被打乱，导致排名计算错误。

而正常运行的Fiddle #1很可能是关闭了derived_merge，让派生表保持独立执行，保证了排序和变量赋值的顺序符合预期。

验证和解决步骤： #

1. 检查当前环境的优化器配置
   在异常的Fiddle #2或生产服务器上执行：

   SELECT @@optimizer_switch;
   

   查看输出中derived_merge的值是on还是off。如果是on，那就是问题所在。

2. 临时关闭derived_merge测试
   执行以下语句修改会话级配置，然后重新运行你的查询：

   SET optimizer_switch='derived_merge=off';
   

   如果查询恢复正常，就确认了是这个配置导致的问题。

3. 修改查询结构避免依赖优化器行为（更稳妥的方案）
   如果你不想依赖配置修改，可以调整查询写法，强制优化器保留排序后的执行顺序：

   SELECT name, rank, position 
   FROM (
       SELECT 
           name, 
           position, 
           @rank := IF(@prev = name, @rank + 1, 1) AS rank, 
           @prev := name 
       FROM (
           -- 先明确获取排序后的数据集
           SELECT drivers.name, results.position
           FROM drivers 
           LEFT JOIN results ON drivers.id = results.driver_id 
           ORDER BY drivers.name, results.position ASC
       ) AS temp
       -- 将变量初始化与排序后的数据集关联，确保赋值顺序
       CROSS JOIN (SELECT @rank := 1, @prev := '') AS init
   ) AS derived 
   WHERE rank <= 3 
   ORDER BY name, rank;
   

   这种写法把排序逻辑完全隔离在最内层，变量初始化和赋值放在外层，减少优化器干扰的可能性。

其他可能的配置差异（概率较低） #

• sql_mode：如果异常环境开启了ONLY_FULL_GROUP_BY以外的严格模式？不过你的查询没有GROUP BY，所以可能性很低。
• join顺序优化：优化器调整了表的连接顺序，导致排序时机提前/延后，但这种情况通常也和derived_merge相关。

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