轉(zhuǎn)載自：https://blog.csdn.net/lxz3000/article/details/6173873

MySQL可以通過(guò)field()函數(shù)自定義排序

格式：
field(value,str1,str2,str3,str4)

value與str1、str2、str3、str4比較，返回1、2、3、4，如遇到null或者不在列表中的數(shù)據(jù)則返回0. 這個(gè)函數(shù)好像Oracle中沒(méi)有專門提供（也可能是我沒(méi)有用到），不過(guò)自己實(shí)現(xiàn)這樣一個(gè)函數(shù)還是比較簡(jiǎn)單的。

mysql> select * from driver_log order by name;+--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2006-08-30 | 152 | | 9 | Ben | 2006-09-02 | 79 | | 5 | Ben | 2006-08-29 | 131 | | 8 | Henry | 2006-09-01 | 197 | | 6 | Henry | 2006-08-26 | 115 | | 4 | Henry | 2006-08-27 | 96 | | 3 | Henry | 2006-08-29 | 300 | | 10 | Henry | 2006-08-30 | 203 | | 7 | Suzi | 2006-09-02 | 502 | | 2 | Suzi | 2006-08-29 | 391 | +--------+-------+------------+-------+10 rows in set (0.00 sec) mysql> select * from driver_log order by field(name,'Suzi','Ben','Henry');+--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 2 | Suzi | 2006-08-29 | 391 | | 7 | Suzi | 2006-09-02 | 502 | | 1 | Ben | 2006-08-30 | 152 | | 9 | Ben | 2006-09-02 | 79 | | 5 | Ben | 2006-08-29 | 131 | | 8 | Henry | 2006-09-01 | 197 | | 6 | Henry | 2006-08-26 | 115 | | 4 | Henry | 2006-08-27 | 96 | | 3 | Henry | 2006-08-29 | 300 | | 10 | Henry | 2006-08-30 | 203 | +--------+-------+------------+-------+10 rows in set (0.00 sec) mysql> select * from driver_log order by field(name,'Suzi','Ben');+--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 10 | Henry | 2006-08-30 | 203 | | 8 | Henry | 2006-09-01 | 197 | | 6 | Henry | 2006-08-26 | 115 | | 4 | Henry | 2006-08-27 | 96 | | 3 | Henry | 2006-08-29 | 300 | | 7 | Suzi | 2006-09-02 | 502 | | 2 | Suzi | 2006-08-29 | 391 | | 5 | Ben | 2006-08-29 | 131 | | 9 | Ben | 2006-09-02 | 79 | | 1 | Ben | 2006-08-30 | 152 | +--------+-------+------------+-------+10 rows in set (0.00 sec) mysql> select * from driver_log order by field(name,'Suzi','Ben') desc;+--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2006-08-30 | 152 | | 9 | Ben | 2006-09-02 | 79 | | 5 | Ben | 2006-08-29 | 131 | | 2 | Suzi | 2006-08-29 | 391 | | 7 | Suzi | 2006-09-02 | 502 | | 8 | Henry | 2006-09-01 | 197 | | 6 | Henry | 2006-08-26 | 115 | | 4 | Henry | 2006-08-27 | 96 | | 3 | Henry | 2006-08-29 | 300 | | 10 | Henry | 2006-08-30 | 203 | +--------+-------+------------+-------+10 rows in set (0.00 sec)

20180911更新內(nèi)容：

我們都非常習(xí)慣通過(guò) MySQL 的 IN 函數(shù)來(lái)查詢特定集合的數(shù)據(jù)，比如為了在 books 表中找出李雷、韓梅梅和安華寫(xiě)的書(shū)，我們可以有如下的 SQL：

SELECT * FROM books WHERE `books`.`author` IN ('李雷','韓梅梅','安華');

數(shù)據(jù)庫(kù)返回如下結(jié)果：

authortitle

安華	暴走漫畫(huà)
李雷	藍(lán)色生死戀
韓梅梅	冰與火之歌
韓梅梅	天國(guó)的階梯
李雷	這個(gè)殺手不太冷
韓梅梅	阿甘正傳

雖然這樣確實(shí)能夠返回所有李雷、韓梅梅和安華寫(xiě)過(guò)的書(shū)，但是返回的數(shù)據(jù)的排序方式是默認(rèn)按照數(shù)據(jù)在數(shù)據(jù)庫(kù)中的存儲(chǔ)順序，假如我們需要的返回結(jié)果是同時(shí)按照 IN 查詢條件里邊的參數(shù)順序來(lái)排序呢？這個(gè)時(shí)候我們就需要利用到 MySQL FIELD 這個(gè)函數(shù)了，FIELD 函數(shù)本來(lái)是 MySQL 提供用來(lái)查詢某一個(gè)字符串在給定字符串元組中的索引位置的，比如這個(gè)官方例子：

SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); # -> 2

如果將其應(yīng)用在 ORDER BY 排序條件中，就可以根據(jù)指定字段的值在給定參數(shù)列表中的索引數(shù)值，進(jìn)而將查詢結(jié)果按照參數(shù)列表排序了

SELECT * FROM books WHERE `books`.`author` IN ('李雷','韓梅梅','安華') ORDER BY FIELD(author, '李雷','韓梅梅','安華'); authortitle

李雷	藍(lán)色生死戀
李雷	這個(gè)殺手不太冷
韓梅梅	冰與火之歌
韓梅梅	天國(guó)的階梯
韓梅梅	阿甘正傳
安華	暴走漫畫(huà)

可以發(fā)現(xiàn)，這一次，我們得到的結(jié)果就是按照條件參數(shù)列表 ‘李雷’,’韓梅梅’,’安華’ 進(jìn)行排序后得到了。

應(yīng)用層面的思考

1. 兼容性
本文提到的 FIELD 函數(shù)，畢竟只是 MySQL 數(shù)據(jù)庫(kù)內(nèi)置提供的一種函數(shù)，除非你非常明確你的項(xiàng)目就是只用 MySQL 數(shù)據(jù)庫(kù)，否則，你的 SQL 代碼在未來(lái)遷移到其他數(shù)據(jù)庫(kù)的過(guò)程中就會(huì)遇到語(yǔ)法兼容性問(wèn)題（只是 PostgreSQL 數(shù)據(jù)庫(kù)不支持 FIELD）。

2. 性能問(wèn)題
我們都知道，數(shù)據(jù)庫(kù)在進(jìn)行 ORDER BY 排序的時(shí)候，除非它是按照某個(gè)已經(jīng)存在索引的鍵的值進(jìn)行排序，否則數(shù)據(jù)庫(kù)則需要通過(guò)計(jì)算 ORDER BY 中表達(dá)式的值并且按照查詢結(jié)果建立新的臨時(shí)表，這個(gè)過(guò)程會(huì)帶來(lái)額外的時(shí)間開(kāi)銷跟內(nèi)存開(kāi)銷，對(duì)數(shù)據(jù)庫(kù)本身就是一種性能負(fù)擔(dān)。這樣的方式在單一數(shù)據(jù)庫(kù)多個(gè)數(shù)據(jù)庫(kù)客戶端連接的時(shí)候，可能對(duì)數(shù)據(jù)庫(kù)造成太大負(fù)擔(dān)。

3. 與應(yīng)用層代碼的結(jié)合
盡管使用 FIELD 函數(shù)可能帶來(lái)兼容性以及性能方面的隱患，但是 FIELD 的使用并非全是有損之處。

比如在與 Ruby 的 active_record 結(jié)合時(shí)，這種通過(guò)數(shù)據(jù)庫(kù)直接完成排序等 SQL 語(yǔ)句可以方便我們構(gòu)建 ActiveRecord::Relation 對(duì)象，因?yàn)槲覀儾辉傩枰葘⒉樵兘Y(jié)果集從內(nèi)存中轉(zhuǎn)為數(shù)組排序，再進(jìn)行二次查詢，可以幫助我們減少 N+1 查詢問(wèn)題，后者也是常見(jiàn)的影響數(shù)據(jù)庫(kù)服務(wù)器性能的現(xiàn)象之一。除此之外，這樣的寫(xiě)法也可以有效地幫助我們簡(jiǎn)化代碼，保持代碼簡(jiǎn)潔。

但是在不需要對(duì)數(shù)據(jù)進(jìn)行二次查詢或者查詢數(shù)據(jù)量太大的情況下，我反而建議可以通過(guò) Ruby 的 Array#sort_by 方法對(duì)數(shù)據(jù)進(jìn)行排序，這樣的話，排序的任務(wù)就轉(zhuǎn)移給了客戶端代碼，排序任務(wù)的壓力就自然分散，減輕了服務(wù)器端的壓力。

總結(jié)

FIELD 函數(shù)結(jié)合 ORDER BY 可以幫助我們將查詢結(jié)果集按照參數(shù)列表順序返回；

FIELD 函數(shù)結(jié)合 ORDER BY 的方式可以幫助我們?cè)跀?shù)據(jù)庫(kù)層面完成排序，簡(jiǎn)化了業(yè)務(wù)代碼邏輯；

FIELD 函數(shù)結(jié)合 ORDER BY 可能帶來(lái) SQL 兼容性以及性能方面的問(wèn)題；
在確認(rèn)項(xiàng)目數(shù)據(jù)庫(kù)不大可能為 MySQL 之外的數(shù)據(jù)庫(kù)的前提下，查詢數(shù)據(jù)量少或者需要保持業(yè)務(wù)代碼簡(jiǎn)潔的場(chǎng)景下，我建議可以采用 FIELD 函數(shù)排序；而在數(shù)據(jù)量龐大的情況下，或者不大可能出現(xiàn)大量 N+1 查詢的情況下，我建議可以采用先在數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)集（只查詢 IN 條件，不排序）再到內(nèi)存中通過(guò)業(yè)務(wù)代碼排序(比如 Ruby 的 Array#sort_by)的方式。

總結(jié)

以上是生活随笔為你收集整理的MySQL自定义排序函数FIELD()的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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