最近業(yè)務反饋一個查詢異常的問題，需要DBA對查詢結果異常給出解釋，并幫助他們解決該問題。問題本質(zhì)是一個組內(nèi)排序取最大值的問題，根據(jù)業(yè)務需求，我構建了測試用例

測試用例--建表

create?table?testorder

(id?int?not?null,

no?int?not?null,

name?char(10)?not?null,

primary?key(id)

)engine=innodb;

--寫入數(shù)據(jù)

insert?into?testorder?values?(1,1,'Mike'),(2,2,'John'),(3,3,'wyett'),(4,4,'Herry'),(5,5,'Mike'),(6,1,'John'),(7,2,'John'),(8,1,'Mike'),(9,1,'Mike');

--查詢1

select?*?from?testorder;

+----+----+-------+

|?id?|?no?|?name??|

+----+----+-------+

|??1?|??1?|?Mike??|

|??2?|??2?|?John??|

|??3?|??3?|?wyett?|

|??4?|??4?|?Herry?|

|??5?|??5?|?Mike??|

|??6?|??1?|?John??|

|??7?|??2?|?John??|

|??8?|??1?|?Mike??|

|??9?|??1?|?Mike??|

+----+----+-------+

--查詢2

select?*?from?testorder?order?by?no?desc;

+----+----+-------+

|?id?|?no?|?name??|

+----+----+-------+

|??5?|??5?|?Mike??|

|??4?|??4?|?Herry?|

|??3?|??3?|?wyett?|

|??2?|??2?|?John??|

|??7?|??2?|?John??|

|??1?|??1?|?Mike??|

|??6?|??1?|?John??|

|??8?|??1?|?Mike??|

|??9?|??1?|?Mike??|

+----+----+-------+

--查詢3select?*?from?(select?id,no,name?from?testorder?order?by?no?desc)a?group?by?a.name;

查詢3這條SQL是我們需要討論的內(nèi)容，也是業(yè)務線為實現(xiàn)組內(nèi)排序取最大值所采用的SQL。標準的程序員反饋問題方式：XXX時間點之前查詢時正常的，這之后突然就不正常了，你們DBA是不是做什么改動了？我把數(shù)據(jù)恢復到自己的測試機，返回值也是正常的。暫且不去管姿勢是否正確，對這條SQL的分析，我們其實可以看出：(1)程序員期待group by執(zhí)行結果是按照臨時表a的數(shù)據(jù)順序來取值；(2)程序員未考慮版本因素，數(shù)據(jù)量變化的因素；為此，我構建了上面的測試用例。

測試

在不同版本的MySQL來進行測試：發(fā)現(xiàn)在Percona 5.5，Percona 5.1，MySQL 5.6關閉sql_mode= ONLY_FULL_GROUP_BY，MySQL5.1等版本下，返回值確如程序員期待的順序，按照order by no desc的順序，相同name返回no值最大的數(shù)據(jù)；+----+----+-------+

|?id?|?no?|?name??|

+----+----+-------+

|??4?|??4?|?Herry?|

|??2?|??2?|?John??|

|??5?|??5?|?Mike??|

|??3?|??3?|?wyett?|

+----+----+-------+

在mysql5.7，關閉sql_mode= ONLY_FULL_GROUP_BY和mariadb 10.*版本中，相同的name值，返回則是取了最早寫入的數(shù)據(jù)行，忽略了order by no desc，按照數(shù)據(jù)的邏輯存儲順序來返回；+----+----+-------+

|?id?|?no?|?name??|

+----+----+-------+

|??4?|??4?|?Herry?|

|??2?|??2?|?John??|

|??1?|??1?|?Mike??|

|??3?|??3?|?wyett?|

+----+----+-------+

其實在這里，SQL等價于select id,no,name from testorder group by name。這里我們看出不同版本的返回值是不同的，先擱置數(shù)據(jù)量的變化引起執(zhí)行結果不同的討論，因為數(shù)據(jù)量大小很難測試。

官方文檔

對上面的測試結果，在官方文檔上，有如下的參考If?ONLY_FULL_GROUP_BY?is?disabled...In?this?case,?the?server?is?free?to?choose?any?value?from?each?group,

so?unless?they?are?the?same,?the?values?chosen?are?indeterminate,?which?is?probably?not?what?you?want.

Furthermore,?the?selection?of?values?from?each?group?cannot?be?influenced?by?adding?an?ORDER?BY?clause.

Result?set?sorting?occurs?after?values?have?been?chosen,?and?ORDER?BY?does?not?affect?which?value?within

each?group?the?server?chooses.

ONLY_FULL_GROUP_BY這個SQL_MODE出在mysql5.6(mariadb 10.0)時被引入，但本文討論的內(nèi)容和它無關，具體可以自己查看文檔，這里不做討論。在5.6,5.5的官方文檔有相同的內(nèi)容，Mariadb也有類似的解釋If?you?select?a?non-grouped?column?or?a?value?computed?from?a?non-grouped?column,?it?is?undefined

which?row?the?returned?value?is?taken?from.?This?is?not?permitted?if?the?ONLY_FULL_GROUP_BY?SQL_MODE?is?used.

并且，對from后的subquery子表中的order by也給出了解釋A?query?such?as

SELECT?field1,?field2?FROM?(?SELECT?field1,?field2?FROM?table1?ORDER?BY?field2?)?alias

returns?a?result?set?that?is?not?necessarily?ordered?by?field2.?This?is?not?a?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.

好了，有了這些解釋，問題很明朗：在from 后的subquery中的order by會被忽略

group by cloumn返回的行是無序的

因此，業(yè)務獲得的正確的返回值也是誤打誤撞。

解決辦法

那么這個問題該怎么解決？

在網(wǎng)上有一些SQL，很明顯不滿足需求，在這里做一下展示，希望同學們避免被誤導：

錯誤SQL集合select?id,sbustring(GROUP_CONCAT(distinct?no?order?by?no?desc?separator?''),'',1),name?from?testorder?group?by?name;--通過添加索引來影響返回的結果集順序

alter?table?testorder?add?index?idx_no_name(no?desc,?name);

--結果證明即使如此，desc也不會被正確執(zhí)行；--我司程序員的寫法

select?*?from?(select?id,no,name?from?testorder?order?by?no?desc)a?group?by?a.nameselect?id,max(no),name?from?testorder?group?by?name

我們可以這樣寫,雖然效率不高select?a.id,a.no,a.name

from?testorder?a

inner?join?(select?max(no)?no,name

from?testorder

group?by?name)?b?on?a.no=b.no?and?a.name=b.name

group?by?name,no

或者這樣select?a.id,a.no,a.name

from?testorder?a

group?by?a.name,a.no

having?a.no=(select?max(no)?from?testorder?where?name=a.name)

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結

以上是生活随笔為你收集整理的mysql 组内排序_mysql组内排序取最大值的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。