本文介紹了相同的查詢和數據結構，但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?中的一些指針，了解如何在不使用變量的情況下重寫查詢。
這篇關于相同的查詢和數據結構，但MySQL 8返回的結果與MySQL 5不同？的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
