MySQL子查詢中order by不生效問題的如何解決

這篇文章主要介紹“MySQL子查詢中order by不生效問題的如何解決”，在日常操作中，相信很多人在MySQL子查詢中order by不生效問題的如何解決問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL子查詢中order by不生效問題的如何解決”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

一個偶然的機會，發現一條SQL語句在不同的MySQL實例上執行得到了不同的結果。

問題描述

創建商品表product_tbl和商品操作記錄表product_operation_tbl兩個表，來模擬下業務場景，結構和數據如下：

接下來需要查詢所有商品最新的修改時間，使用如下語句：

select t1.id, t1.name, t2.product_id, t2.created_at  from product_tbl t1 left join (select * from product_operation_log_tbl order by created_at desc) t2 on t1.id = t2.product_id group by t1.id;

通過結果可以看到，子查詢先將product_operation_log_tbl里的所有記錄按創建時間(created_at)逆序，然后和product_tbl進行join操作，進而查詢出的商品的最新修改時間。

在區域A的MySQL實例上，查詢商品最新修改時間可以得到正確結果，但是在區域B的MySQL實例上，得到的修改時間并不是最新的，而是最老的。通過對語句進行簡化，發現是子查詢中的order by created_at desc語句在區域B的實例上沒有生效。

排查過程

難道區域會影響MySQL的行為？經過DBA排查，區域A的MySQL是5.6版，區域B的MySQL是5.7版，并且找到了這篇文章：

https://blog.csdn.net/weixin_42121058/article/details/113588551

根據文章的描述，MySQL 5.7版會忽略掉子查詢中的order by語句，可令人疑惑的是，我們模擬業務場景的MySQL是8.0版，并沒有出現這個問題。使用docker分別啟動MySQL 5.6、5.7、8.0三個實例，來重復上面的操作，結果如下：

可以看到，只有MySQL 5.7版忽略了子查詢中的order by。有沒有可能是5.7引入了bug，后續版本又修復了呢？

問題根因

繼續搜索文檔和資料，發現官方論壇中有這樣一段描述：

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order). You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

問題的原因清晰了，原來SQL標準中，table的定義是一個未排序的數據集合，而一個SQL子查詢是一個臨時的table，根據這個定義，子查詢中的order by會被忽略。同時，官方回復也給出了解決方案：將子查詢的order by移動到最外層的select語句中。

總結

在SQL標準中，子查詢中的order by是不生效的

MySQL 5.7由于在這個點上遵循了SQL標準導致問題暴露，而在MySQL 5.6/8.0中這種寫法依然是生效的

到此這篇關于MySQL子查詢中order by不生效問題的文章就介紹到這了,更多相關MySQL子查詢order by不生效內容請搜索億速云以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持億速云！

參考文檔

https://stackoverflow.com/questions/26372511/mysql-mariadb-order-by-inside-subquery

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

到此，關于“MySQL子查詢中order by不生效問題的如何解決”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！