MySQL 查詢結果自定義排序：FIELD()、FIND_IN_SET() 與 ORDER BY 實戰技巧

一、業務場景：指定值優先排序

需求：查詢結果中 name='lucy' 的記錄優先顯示，其餘按預設順序。

-- 原始數據
id | name
1  | lucy
2  | lily
3  | lucy
4  | lucy

-- 期望結果
id | name
1  | lucy
3  | lucy
4  | lucy
2  | lily

二、方案對比

2.1 FIND_IN_SET() + DESC

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

• FIND_IN_SET(str, strlist)：返回 str 在逗號分隔的 strlist 中的位置（1-based），不存在返回 0
• lucy → 1，lily → 0，DESC 讓 1 排前面
• 缺點：strlist 為常數字符串，不支援動態列表；含逗號的值會解析錯誤

2.2 FIELD() 函數（推薦）

SELECT * FROM test 
ORDER BY FIELD(name, 'lucy') DESC;

• FIELD(str, str1, str2, ...)：返回 str 在列表中的位置，不存在返回 0
• 支援多值優先級：FIELD(status, 'urgent', 'high', 'normal', 'low')
• 無逗號解析問題，語義更清晰

2.3 IN (...) 配合 FIELD() 保持指定順序

  方案執行計劃特徵效能評級適用場景
  ORDER BY FIELD()Using filesort⚠️ 中等小結果集（<1萬行）、低頻查詢
  ORDER BY FIND_IN_SET()Using filesort⚠️ 中等同左，但不支援動態列表
  UNION ALL 分優先級查詢多次 ref/const 掃描✅ 優大表、高頻、有適當索引
  應用層排序無額外 DB 負載✅ 最優結果集可在記憶體處理


優化建議

  
小表/低頻：直接用 ORDER BY FIELD(...)，代碼簡潔
  
大表/高頻：改用 UNION ALL 分優先級查詢，每段走索引：
    
(SELECT * FROM test WHERE name='lucy' ORDER BY id LIMIT 100)
    UNION ALL
    (SELECT * FROM test WHERE name!='lucy' ORDER BY id LIMIT 100)
  
  
結果集 < 1000 行：應用層排序（Java Comparator、Python sorted()）最靈活
  
避免在大表無索引列上使用 FIELD() 排序，會觸發全表掃描 + filesort


四、進階技巧：CASE WHEN 排序
SELECT * FROM test
ORDER BY CASE 
  WHEN name='lucy' THEN 1
  WHEN name='lily' THEN 2
  ELSE 3
END, id;

  
邏輯最靈活，支援複雜條件組合
  
同樣會產生 Using filesort


五、總結

  
自定義排序首選 FIELD()，語義清晰、無逗號陷阱
  
大表高頻場景**務必在應用層或 UNION ALL 解決**，避免 filesort 拖垮資料庫
  
配合 LIMIT 使用時，FIELD() 排序會先全量排序再截取，效能陷阱
  
EXPLAIN 觀察 Using filesort 即為排序無法走索引的信號