今天遇到一個問題 就是有個查詢需要按照指定的 ID 值順序來返回結果集 其實也可以放在程式中做排序 但是突然想看看能不能直接使用Mysql直接查詢返回 就找了下 還真有輔助函式實現
Field()函式
Mysql中有提供一個函式 Field() 可以按照我們給定的順序來自定義排序
示例：
假設現在有張城市資訊表 叫 regions 有 主鍵 id 和 一個名稱屬性 name, 現在想查詢 ID 為 2、3、1 並按照這個順序返回
select id, name from regions;
#id name
1 北京
2 上海
3 深圳使用 field()
select id, name from regions order by field(id, 2, 3, 1);
#id name
2 上海
3 深圳
1 北京這樣就達到按按自定義順序排序的目的了
效能
mysql> explain select id from regions order by field(id, 2, 3, 1);
+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+
|id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|-- | ----------- | ------- | ---- | ------------- | --- | ------- | --- | ---- | ----------------------------|
|1 | SIMPLE | regions | index| NULL | id | 4 | NULL| 3 | Using index; Using filesort |
+---+-------------+---------+------+---------------+-----+---------+-----+------+-----------------------------+因為我們在使用 Order By Field 的時候指定了是按照 主鍵ID 來排序 主鍵有個 Primary 的主鍵索引 他會使用id來尋找條件等於 2,3,1 的記錄 所以可以看到在 Extra 中有 Using index 如果你換個別的沒有索引的欄位這裡就不會有它了。而 Order By 子句不能使用該索引 只能使用 Filesort 排序 也就是 Extra 中有 Using filesort 的原因
大概過程如下：
從id索引的第一個葉子節點出發，按順序掃描所有葉子節點
根據每個葉子節點記錄的主鍵id去主鍵索引(聚簇索引))找到真實的行資料
判斷行資料是否滿足 id = 2、3、1 條件，若滿足，則取出並返回
基本要遍歷全表了 有人說 它把選出的記錄的 id 在 FIELD 列表中進行查詢，並返回位置，以位置作為排序依據。
這樣的用法，會導致 Using filesort（當然使用了Filesort 並不一定就會慢 有時候比不是用要更快），是效率很低的排序方式。
通常ORDER BY子句會與LIMIT子句配合，只取出部分行。如果只是為了取出top1的行 卻對所有行進行排序，這顯然不是一種高效的做法。
總結
Field() 函式可以幫助我們在資料庫層直接完成一些需要的排序 可以簡化業務程式碼，但是同時它還會有相容性和效能問題 建議可以用在資料變化頻率低 或者有長時間快取的地方，而在資料量很大的情況下 可以採用資料庫查詢出資料在到程式中來排序吧
本作品採用《CC 協議》，轉載必須註明作者和本文連結