从兼容MySQL5的Aurora迁到MySQL8后查询结果不一致如何解决

这个问题的核心是你用用户自定义变量实现层级递归查询的写法，依赖的是MySQL 5.x版本未做官方承诺的执行顺序特性，MySQL 8.0对优化器和变量逻辑做了不兼容调整，导致原有逻辑失效，具体原因如下：

• 用户自定义变量的执行顺序不再保证：MySQL 8.0优化器会对无明确副作用的变量表达式调整执行顺序，你原有逻辑中@pv拼接、@r递归赋值的执行顺序被打乱，匹配层级节点的逻辑直接失效。
• 派生表排序默认被忽略：SQL标准规定不带LIMIT的子查询/派生表的ORDER BY不影响外层结果，MySQL 8.0会默认忽略这类排序，你原有逻辑依赖的parent_account_id, account_id排序前提消失，变量遍历节点的顺序完全不符合预期。
• 跨UNION分支的变量作用域调整：MySQL 8.0对UNION分支之间的用户变量共享逻辑做了修改，你两个UNION分支都初始化了@l、@cl等变量，作用域覆盖规则和MySQL 5.x不一致，进一步导致递归计数和匹配错误。

临时兼容方案（快速对齐旧版本结果） #

• 临时关闭派生表合并优化，执行语句SET optimizer_switch = 'derived_merge=off';，阻止优化器修改你的子查询结构。
• 给所有带ORDER BY的子查询加上足够大的LIMIT，比如LIMIT 999999，强制优化器保留子查询的排序逻辑。
• 调整变量赋值的书写顺序，用IF函数强制赋值时机，比如将子查询匹配条件修改为WHERE account_id = @pv OR (find_in_set(parent_account_id, @pv) > 0 AND IF(@pv := concat(@pv, ',', account_id), 1, 1))，保证匹配到节点后才更新变量。

长期兼容方案（彻底规避兼容性问题） #

• 替换为MySQL 8.0官方支持的标准递归CTE写法，完全不需要依赖用户自定义变量，结果稳定可控，参考写法如下：

WITH RECURSIVE account_hierarchy AS (
  -- 初始节点
  SELECT account_id, parent_account_id, 1 AS level, 'current' AS node_type FROM Account WHERE account_id = 520
  UNION ALL
  -- 递归查询所有子节点
  SELECT a.account_id, a.parent_account_id, ah.level + 1 AS level, 'child' AS node_type
  FROM Account a INNER JOIN account_hierarchy ah ON a.parent_account_id = ah.account_id
  UNION ALL
  -- 递归查询所有父节点
  SELECT a.account_id, a.parent_account_id, ah.level + 1 AS level, 'parent' AS node_type
  FROM Account a INNER JOIN account_hierarchy ah ON a.account_id = ah.parent_account_id
)
SELECT account_id FROM account_hierarchy ORDER BY IF(node_type='child', level, 999) DESC, IF(node_type='parent', level, 999) ASC;

通用迁移校验方法 #

• 迁移前跑全量业务SQL回归，将所有业务查询在新旧版本执行后对比结果集差异，提前发现不兼容逻辑。
• 提前排查所有非标准SQL特性：除了用户自定义变量递归外，还包括非严格SQL模式下的GROUP BY隐式分组、旧密码函数、MyISAM引擎特有特性等，逐一替换为SQL标准写法。
• 上线初期开启MySQL 8.0的MySQL 5.7兼容模式，Aurora MySQL 8可执行SET sql_mode = 'MYSQL57_COMPAT'，先对齐旧版本行为再逐步改造不兼容SQL。

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