含赋值运算符与变量的MySQL查询在5.7可用但8.0无结果

问题说明 #

我有一条用于查询单表4级层级数据的SQL，传入最底层节点ID后可获取该节点及其所有父节点（共4条数据），但该语句仅在MySQL 5.7中正常运行，在MySQL 8.0中无报错但无结果返回。

数据库示例数据 #

MySQL 5.7可用查询语句 #

该语句在MySQL 5.7中可正确返回目标结果：

SELECT id, type, title FROM ( SELECT @r AS _id, (SELECT @r := parent FROM category WHERE id = _id) AS parent, @l := @l + 1 AS lvl 
FROM (SELECT @r := 4, @l := 0) vars, category h WHERE @r <> 0) T1 
JOIN category T2 ON T1._id = T2.id 
ORDER BY T1.lvl DESC

MySQL 5.7执行结果 #

MySQL 8.0失效原因 #

根据官方文档，MySQL 5.7和8.0中，自增用户变量的行为均不被保证：

SET @a = @a + 1; 对于SELECT等其他语句，您可能会得到预期结果，但这并不被保证。对于以下语句，您可能认为MySQL会先计算@a的值再进行赋值...

另外，从MySQL 8.0.22开始：

预处理语句中对用户变量的引用会在首次预处理时确定类型，且后续每次执行都保留该类型。类似地，存储过程中语句使用的用户变量类型会在首次调用存储过程时确定，后续每次调用都保留该类型。

这两点就是导致原查询在MySQL 8.0中失效的核心原因。

MySQL 8.0原生替代方案 #

MySQL 8.0支持递归CTE，可以实现相同功能，但无法直接兼容5.7版本：

WITH RECURSIVE parent_cats (id, parent, title, type) AS (
  SELECT id, parent, title, type
  FROM category
  WHERE id = 4
  UNION ALL
  SELECT t.id, t.parent, t.title, t.type
  FROM category t INNER JOIN parent_cats pc
  ON t.id = pc.parent
)
SELECT * FROM parent_cats;

跨版本兼容解决方案 #

要同时兼容MySQL 5.7和8.0，可采用以下两种方案：

方案1：基于版本判断的存储过程（推荐） #

通过VERSION()函数识别MySQL版本，动态执行对应查询逻辑，这是最可靠的兼容方式：

DELIMITER //
CREATE PROCEDURE get_node_hierarchy(IN target_id INT)
BEGIN
  -- 判断是否为MySQL 8.0及以上版本
  IF SUBSTRING_INDEX(VERSION(), '.', 2) >= '8.0' THEN
    WITH RECURSIVE parent_cats (id, parent, title, type) AS (
      SELECT id, parent, title, type
      FROM category
      WHERE id = target_id
      UNION ALL
      SELECT t.id, t.parent, t.title, t.type
      FROM category t INNER JOIN parent_cats pc ON t.id = pc.parent
    )
    SELECT id, type, title FROM parent_cats ORDER BY type ASC;
  ELSE
    -- 执行MySQL 5.7兼容的变量查询
    SELECT id, type, title FROM (
      SELECT @r AS _id, (SELECT @r := parent FROM category WHERE id = _id) AS parent, @l := @l + 1 AS lvl 
      FROM (SELECT @r := target_id, @l := 0) vars, category h WHERE @r <> 0
    ) T1 
    JOIN category T2 ON T1._id = T2.id 
    ORDER BY T1.lvl DESC;
  END IF;
END //
DELIMITER ;

调用方式：CALL get_node_hierarchy(4);

方案2：调整变量顺序的兼容写法（不推荐） #

虽然用户变量行为不被官方保证，但调整变量赋值顺序后，部分MySQL 8.0环境可正常运行原逻辑：

SELECT id, type, title 
FROM (
  SELECT 
    @l := @l + 1 AS lvl,
    @r AS _id,
    @r := (SELECT parent FROM category WHERE id = @r) AS parent
  FROM (SELECT @r := 4, @l := 0) vars, category h 
  WHERE @r <> 0
) T1 
JOIN category T2 ON T1._id = T2.id 
ORDER BY T1.lvl DESC;

注意：该写法依赖未被官方承诺的行为，未来版本可能再次失效，仅作为临时过渡方案。

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