相同的查询和数据结构，但MySQL 8返回的结果与MySQL 5不同？

我已将数据库从AWS Aurora 1.22.3(与MySQL 5.6兼容)迁移到MySQL 8，并有一个查询返回帐户及其父帐户(如果是父帐户，则返回子帐户)。

帐户表将包含：

Account_id(主键)、Parent_Account_id(外键) 帐户名，...

例如，我的帐户520具有父帐户519：

account_id,tenant_id,parent_account_id,account_name,account_code,account_class,account_type,account_description,status,is_master_account,currency_code,tax_type,pending_balance,authorised_balance,total_balance,related_party_id,creator_id
519,1,NULL,"SANTOS LIMITED - Trade1",000604,ASSET,FINRECEIVABLE,NULL,ACTIVE,1,AUD,BASEXC,0.000000,0.000000,0.000000,321,1
520,1,519,"SANTOS LIMITED - Trade Card1",000604-1,ASSET,FINRECEIVABLE,NULL,ACTIVE,0,AUD,BASEXC,0.000000,0.000000,0.000000,321,1

以下是我的问题：

    SELECT 
        t_all.account_id -- , parent_level, t_all.level 
    FROM ( 
        -- GET ALL CHILDREN 
        SELECT 
            account_id, 
            parent_account_id, 
            null as parent_level, 
            (@l:=@l + 1) AS level 
        FROM 
        ( 
            SELECT 
                account_id, 
                tenant_id, 
                parent_account_id 
            FROM Account 
            ORDER BY 
                parent_account_id, 
                account_id 
        ) account_sorted, 
        ( 
            SELECT @pv := 520, @l := 0, @cl := 0 
        ) initialisation 
        WHERE 
        account_id = @pv OR 
        find_in_set(parent_account_id, @pv) > 0 
        AND 
        @pv := concat(@pv, ',', account_id) 
        
        UNION 
        
        -- GET ALL PARENTS 
        SELECT 
            account_id, 
            parent_account_id, 
            level as parent_level, 
            null as level 
        FROM 
        ( 
            SELECT 
                _id AS account_id, 
                parent_account_id, 
                @cl := @cl + 1 AS level 
            FROM 
            ( 
                SELECT 
                    @r AS _id, 
                    ( 
                        SELECT @r := parent_account_id 
                        FROM Account 
                        WHERE account_id = @r 
                    ) AS parent_account_id, 
                    @l := @l + 1 AS level 
                FROM 
                ( 
                    SELECT @r := 520, @l := 0, @cl := 0 
                ) vars, 
                Account h 
                WHERE 
                    @r <> 0 
                ORDER BY level DESC 
            ) qi 
        ) qo 
    ) as t_all 
    order by level desc , parent_level asc; 

旧版本(MySQL5)将返回3条记录(520,519,520)，而MySQL8只返回一个帐户id 520。预期输出将与旧版本相同。

您认为是什么原因导致此问题？迁移数据库版本时应如何确保查询结果一致？

非常感谢您的帮助。

---

解决方案

出现这种行为的一般原因是mySQL使用了一些与您使用的变量不兼容的优化。您使用了相当多的变量，所以我不想在这里找出哪些优化对您有特别的影响，但请参见Myanswer here作为一个例子。通常，如果变量的值发生变化，MySQL会对您的子查询做出一些不一定正确的假设。

一般的解决方案是阻止MySQL进行这些优化，目前，这些优化可以普遍通过物化来完成。您可以通过向所有子查询添加任意大限制来实现这一点，例如

... FROM Account 
ORDER BY parent_account_id, account_id 
LIMIT 100000000 -- add this
...

... FROM Account WHERE account_id = @r 
LIMIT 100000000 -- add this
...

这样做的效果是，MySQL将实际生成查询的所有行，并随后(可能)以您希望的方式计算变量，因此您应该会得到预期的结果。(如果你没有，你可能忘记了一些限制，所以试着把它添加到更多的地方)。

一般说来，尽管变量的使用通常和实际上都像预期的那样工作，但在MySQL8之前，变量的使用就已经正式变得脆弱了，例如，参见documentation

对于其他语句，例如SELECT，您可能会得到预期的结果，但这并不能保证。在下面的语句中，您可能认为MySQL首先计算@a，然后再进行赋值：

 SELECT @a, @a:=@a+1, ...;

但是，涉及用户变量的表达式的计算顺序未定义。

还有一个更一般的警告：您使用变量的方式从MySQL8开始就不再推荐使用，并且可能会在MySQL的某些未来版本中导致语法错误。您可能希望查看(recursive) Common Table Expressions和How to create a MySQL hierarchical recursive query?中的一些指针，了解如何在不使用变量的情况下重写查询。