快捷導(dǎo)航

MySQL子查詢(xún)中order by不生效問(wèn)題的解決方法

更新時(shí)間：2021年07月31日 10:49:39 作者：simpleapples

ORDER BY 語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序,在日常工作中經(jīng)常會(huì)用到,這篇文章主要給大家介紹了關(guān)于MySQL子查詢(xún)中order by不生效問(wèn)題的解決方法,需要的朋友可以參考下

一個(gè)偶然的機(jī)會(huì)，發(fā)現(xiàn)一條SQL語(yǔ)句在不同的MySQL實(shí)例上執(zhí)行得到了不同的結(jié)果。

問(wèn)題描述

創(chuàng)建商品表product_tbl和商品操作記錄表product_operation_tbl兩個(gè)表，來(lái)模擬下業(yè)務(wù)場(chǎng)景，結(jié)構(gòu)和數(shù)據(jù)如下：

接下來(lái)需要查詢(xún)所有商品最新的修改時(shí)間，使用如下語(yǔ)句：

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;

通過(guò)結(jié)果可以看到，子查詢(xún)先將product_operation_log_tbl里的所有記錄按創(chuàng)建時(shí)間(created_at)逆序，然后和product_tbl進(jìn)行join操作，進(jìn)而查詢(xún)出的商品的最新修改時(shí)間。

在區(qū)域A的MySQL實(shí)例上，查詢(xún)商品最新修改時(shí)間可以得到正確結(jié)果，但是在區(qū)域B的MySQL實(shí)例上，得到的修改時(shí)間并不是最新的，而是最老的。通過(guò)對(duì)語(yǔ)句進(jìn)行簡(jiǎn)化，發(fā)現(xiàn)是子查詢(xún)中的order by created_at desc語(yǔ)句在區(qū)域B的實(shí)例上沒(méi)有生效。

排查過(guò)程

難道區(qū)域會(huì)影響MySQL的行為？經(jīng)過(guò)DBA排查，區(qū)域A的MySQL是5.6版，區(qū)域B的MySQL是5.7版，并且找到了這篇文章：

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

根據(jù)文章的描述，MySQL 5.7版會(huì)忽略掉子查詢(xún)中的order by語(yǔ)句，可令人疑惑的是，我們模擬業(yè)務(wù)場(chǎng)景的MySQL是8.0版，并沒(méi)有出現(xiàn)這個(gè)問(wèn)題。使用docker分別啟動(dòng)MySQL 5.6、5.7、8.0三個(gè)實(shí)例，來(lái)重復(fù)上面的操作，結(jié)果如下：

可以看到，只有MySQL 5.7版忽略了子查詢(xún)中的order by。有沒(méi)有可能是5.7引入了bug，后續(xù)版本又修復(fù)了呢？

問(wèn)題根因

繼續(xù)搜索文檔和資料，發(fā)現(xiàn)官方論壇中有這樣一段描述：

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.

問(wèn)題的原因清晰了，原來(lái)SQL標(biāo)準(zhǔn)中，table的定義是一個(gè)未排序的數(shù)據(jù)集合，而一個(gè)SQL子查詢(xún)是一個(gè)臨時(shí)的table，根據(jù)這個(gè)定義，子查詢(xún)中的order by會(huì)被忽略。同時(shí)，官方回復(fù)也給出了解決方案：將子查詢(xún)的order by移動(dòng)到最外層的select語(yǔ)句中。

總結(jié)

在SQL標(biāo)準(zhǔn)中，子查詢(xún)中的order by是不生效的

MySQL 5.7由于在這個(gè)點(diǎn)上遵循了SQL標(biāo)準(zhǔn)導(dǎo)致問(wèn)題暴露，而在MySQL 5.6/8.0中這種寫(xiě)法依然是生效的

到此這篇關(guān)于MySQL子查詢(xún)中order by不生效問(wèn)題的文章就介紹到這了,更多相關(guān)MySQL子查詢(xún)order by不生效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！