MySQL 查詢結果自訂排序實戰：FIELD() 與 FIND_IN_SET() 進階用法

業務常需求：查詢結果按特定值順序排列，而非預設的字母或數值順序。例如：將名為「lucy」的記錄優先顯示，或依 IN 列表順序輸出。MySQL 提供 FIELD() 與 FIND_IN_SET() 兩大函式支援自訂排序，本文詳細解析用法、原理與效能考量。

一、FIND_IN_SET()：集合中尋找位置

語法

FIND_IN_SET(str, strlist)

• strlist：以逗號分隔的字串列表（如 'a,b,c'）。
• 回傳 str 在列表中的位置（1 起算），不存在回傳 0，參數為 NULL 回傳 NULL。
• 若 str 含逗號將失效；若第一參數為常數且第二為 SET 型別欄位，MySQL 以位元運算優化。

實例：指定名稱優先

SELECT * FROM test_table ORDER BY FIND_IN_SET(name, 'lucy') DESC;

name='lucy' 回傳 1，其他回傳 0，DESC 使 1 排前面，達到優先效果。

二、FIELD()：列表中回傳索引位置

語法

FIELD(str, str1, str2, str3, ...)

• 回傳 str 在後續參數列表中的索引（1 起算），不存在回傳 0。
• 參數可為欄位或常數，支援數值與字串比較。

實例：按 IN 列表順序輸出

SELECT * FROM table WHERE id IN (3,6,9,1,2,5,8,7) ORDER BY FIELD(id, 3,6,9,1,2,5,8,7);

結果將嚴格依 IN 列表順序排列，而非預設遞增。

三、效能深度解析與最佳實踐

⚠️ 效能陷阱：Using filesort

兩函式均會導致 Using filesort，MySQL 無法利用索引排序，需在記憶體/磁碟臨時表中排序。資料量大時效能急劇下降。

測試數據對比

-- 假設 test 表有 100 萬筆資料
SELECT * FROM test WHERE id IN (3,1,5) ORDER BY FIELD(id,3,1,5);  -- 會觸發 filesort
SELECT * FROM test WHERE id IN (3,1,5);  -- 預設按主鍵遞增，無 filesort

優化建議

1. 小資料集/低頻查詢： 可直接使用 FIELD() 或 FIND_IN_SET()，維護成本低。
2. 大資料集/高頻查詢： 採用應用層排序（取回資料後在 Java/Python/Go 中按指定順序排序），避免資料庫 filesort。
3. 中間方案： 建立映射表（id, sort_order），JOIN 後 ORDER BY sort_order，可利用索引排序。
4. 緩存策略： 若排序規則固定且資料變動低，將排序結果緩存（Redis 等），減少資料庫壓力。

四、常見誤區

• 誤以為 IN 列表順序會影響結果順序： MySQL 優化器會重新排序，預設輸出仍按索引順序（通常為主鍵遞增）。
• 忽略 FIND_IN_SET 逗號限制： 被搜尋字串若含逗號（如 'a,b'），函式失效。
• 濫用於分頁查詢： 深分頁配合 filesort 會極其緩慢，務必改用游標分頁或覆蓋索引。

五、總結

FIELD() 與 FIND_IN_SET() 是實現自訂排序的利器，適用於中小資料集或靈活業務場景。大規模高併發系統建議採用「映射表 + 索引排序」或「應用層排序」架構，避開 filesort 陷阱。選型時請以 EXPLAIN 驗證執行計畫，並結合業務實際壓測決定。