MariaDB升级10.6+后SELECT子查询ORDER BY排序失效如何修复

问题原因 #

MariaDB 10.2及以上版本对查询优化器做了调整：如果子查询没有使用LIMIT子句，优化器会默认忽略子查询内部的ORDER BY逻辑，认为外层查询不需要保证子查询的顺序，所以你原本依赖排序生成序号的逻辑就失效了。

方案1：添加LIMIT保留子查询排序（最小改动） #

给你的子查询加一个超大的LIMIT值，让优化器保留ORDER BY逻辑，改写后的代码如下：

$this->db->query("
    SET @v:=0;
    UPDATE `users` AS `c1`
    LEFT JOIN (SELECT `id`, (@v:=@v+2) AS `ord2`, `name`, `parent` 
    FROM `users` 
    WHERE `parent`='0' ORDER BY `ord` ASC LIMIT 18446744073709551615) AS `c2`
    ON `c1`.`id` = `c2`.`id`
    SET `c1`.`ord` = `c2`.`ord2`
    WHERE `c1`.`parent` = '0'
");

这个LIMIT值是MariaDB支持的最大无符号整数，不会对你的实际数据量产生限制，同时能强制优化器保留排序逻辑。

方案2：使用窗口函数（推荐，更稳定） #

MariaDB 10.2以上已经支持标准SQL窗口函数，不用自定义变量也能实现序号生成，逻辑更稳定，不会因为优化器规则变更失效：

$this->db->query("
    UPDATE `users` AS `c1`
    LEFT JOIN (SELECT `id`, (ROW_NUMBER() OVER(ORDER BY `ord` ASC)) * 2 AS `ord2`
    FROM `users` 
    WHERE `parent`='0') AS `c2`
    ON `c1`.`id` = `c2`.`id`
    SET `c1`.`ord` = `c2`.`ord2`
    WHERE `c1`.`parent` = '0'
");

ROW_NUMBER() OVER(ORDER BY ord ASC)会按ord字段升序生成1、2、3、4的连续序号，乘以2之后就能得到你需要的2、4、6、8的偶数序号，逻辑和你原来的需求完全一致。

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