MySQL in 查詢，並通過 FIELD 函式按照查詢條件順序返回結果

Martin91發表於2015-09-13
MySql函式

從示例講起

我們都非常習慣通過 MySQL 的 IN 函式來查詢特定集合的資料，比如為了在 books 表中找出李雷、韓梅梅和安華寫的書，我們可以有如下的 SQL：

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

注意: 可以通過 SQL Fiddle 檢視以上示例。

資料庫返回如下結果：

author title
安華 暴走漫畫
李雷 藍色生死戀
韓梅梅 冰與火之歌
韓梅梅 天國的階梯
李雷 這個殺手不太冷
韓梅梅 阿甘正傳

雖然這樣確實能夠返回所有李雷、韓梅梅和安華寫過的書，但是返回的資料的排序方式是預設按照資料在資料庫中的儲存順序，假如我們需要的返回結果是同時按照 IN 查詢條件裡邊的引數順序來排序呢？這個時候我們就需要利用到 MySQL FIELD 這個函式了，FIELD 函式本來是 MySQL 提供用來查詢某一個字串在給定字串元組中的索引位置的，比如這個官方例子：

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

如果將其應用在 ORDER BY 排序條件中，就可以根據指定欄位的值在給定引數列表中的索引數值，進而將查詢結果按照引數列表排序了：

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

注意： 可以通過SQL Fiddle檢視以上示例。

這一次，返回的結果則是：

author title
李雷 藍色生死戀
李雷 這個殺手不太冷
韓梅梅 冰與火之歌
韓梅梅 天國的階梯
韓梅梅 阿甘正傳
安華 暴走漫畫

可以發現，這一次，我們得到的結果就是按照條件引數列表 `李雷`,`韓梅梅`,`安華` 進行排序後得到了。

應用層面的思考

1. 相容性

本文提到的 FIELD 函式，畢竟只是 MySQL 資料庫內建提供的一種函式，除非你非常明確你的專案就是隻用 MySQL 資料庫，否則，你的 SQL 程式碼在未來遷移到其他資料庫的過程中就會遇到語法相容性問題（只是 PostgreSQL 資料庫不支援 FIELD）。

2. 效能問題

我們都知道，資料庫在進行 ORDER BY 排序的時候，除非它是按照某個已經存在索引的鍵的值進行排序，否則資料庫則需要通過計算 ORDER BY 中表示式的值並且按照查詢結果建立新的臨時表，這個過程會帶來額外的時間開銷跟記憶體開銷，對資料庫本身就是一種效能負擔。這樣的方式在單一資料庫多個資料庫客戶端連線的時候，可能對資料庫造成太大負擔。

3. 與應用層程式碼的結合

儘管使用 FIELD 函式可能帶來相容性以及效能方面的隱患，但是 FIELD 的使用並非全是有損之處。

比如在與 Ruby 的 active_record 結合時，這種通過資料庫直接完成排序等 SQL 語句可以方便我們構建 ActiveRecord::Relation 物件，因為我們不再需要先將查詢結果集從記憶體中轉為陣列排序，再進行二次查詢，可以幫助我們減少 N+1 查詢問題，後者也是常見的影響資料庫伺服器效能的現象之一。除此之外，這樣的寫法也可以有效地幫助我們簡化程式碼，保持程式碼簡潔。

但是在不需要對資料進行二次查詢或者查詢資料量太大的情況下，我反而建議可以通過 Ruby 的 Array#sort_by 方法對資料進行排序，這樣的話，排序的任務就轉移給了客戶端程式碼，排序任務的壓力就自然分散，減輕了伺服器端的壓力。

總結

  1. FIELD 函式結合 ORDER BY 可以幫助我們將查詢結果集按照引數列表順序返回；

  2. FIELD 函式結合 ORDER BY 的方式可以幫助我們在資料庫層面完成排序，簡化了業務程式碼邏輯；

  3. FIELD 函式結合 ORDER BY 可能帶來 SQL 相容性以及效能方面的問題；

  4. 在確認專案資料庫不大可能為 MySQL
    之外的資料庫的前提下，查詢資料量少或者需要保持業務程式碼簡潔的場景下，我建議可以採用 FIELD 函式排序；而在資料量龐大的情況下，或者不大可能出現大量 N+1 查詢的情況下，我建議可以採用先在資料庫中查詢資料集（只查詢 IN 條件，不排序）再到記憶體中通過業務程式碼排序(比如 Ruby 的 Array#sort_by)的方式。

相關文章