您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 指定 ID 順序輸出結果：FIELD()、INSTR() 與 CASE WHEN 方案對比

編輯：MySQL 綜合教學

MySQL 指定 ID 順序輸出結果：FIELD()、INSTR() 與 CASE WHEN 方案對比

業務常需按指定 ID 列表順序返回結果（如置頂文章、推薦位排序）。MySQL IN 查詢不保證順序，本文對比三種自定義排序方案的效能與適用場景。

問題重現

-- 需按 55,6,5,173,176,174,175,177 順序輸出
SELECT * FROM cms_hit WHERE aid IN (5,6,55,173,177,174,175,176);
-- 結果順序不確定，通常按主鍵/索引順序返回

方案對比

方案	SQL 寫法	原理	效能	適用場景
FIELD() 函數（推薦）	`ORDER BY FIELD(aid, 55,6,5,173,176,174,175,177)`	返回值在列表中的位置索引 (1-based)，不在列表返回 0	⭐⭐⭐⭐⭐ 內建函數，優化器識別度高	ID 列表較短 (< 1000)，可讀性最佳
INSTR() + CONCAT()	`ORDER BY INSTR(',55,6,5,173,176,174,175,177,', CONCAT(',', aid, ','))`	構造帶分隔符字符串，查找子串位置	⭐⭐⭐ 字符串操作開銷大，長列表效能差	舊版本不支援 FIELD() 時備選
CASE WHEN	`ORDER BY CASE aid WHEN 55 THEN 1 WHEN 6 THEN 2 WHEN 5 THEN 3 ... ELSE 999 END`	顯式映射排序權重	⭐⭐⭐⭐ 標準 SQL，跨資料庫兼容	複雜排序邏輯（非單純 ID 列表）
臨時表 JOIN	`JOIN (SELECT 55 AS id, 1 AS ord UNION ALL SELECT 6,2 ...) t ON cms_hit.aid=t.id ORDER BY t.ord`	建立排序映射表關聯	⭐⭐⭐⭐ 大列表 ( > 1000) 效能最穩定	超長列表、動態排序規則

最佳實踐代碼

-- 方案 1：FIELD() - 最推薦
SELECT * FROM cms_hit 
WHERE aid IN (5,6,55,173,177,174,175,176)
ORDER BY FIELD(aid, 55,6,5,173,176,174,175,177);

-- 方案 2：INSTR() - 兼容舊版本
SELECT *, 
       INSTR(',55,6,5,173,176,174,175,177,', CONCAT(',', aid, ',')) AS sort_order
FROM cms_hit 
WHERE aid IN (5,6,55,173,177,174,175,176)
ORDER BY sort_order;

-- 方案 3：CASE WHEN - 標準 SQL
SELECT * FROM cms_hit 
WHERE aid IN (5,6,55,173,177,174,175,176)
ORDER BY CASE aid 
    WHEN 55 THEN 1 WHEN 6 THEN 2 WHEN 5 THEN 3 
    WHEN 173 THEN 4 WHEN 176 THEN 5 WHEN 174 THEN 6 
    WHEN 175 THEN 7 WHEN 177 THEN 8 ELSE 999 END;

-- 方案 4：大列表用臨時表
CREATE TEMPORARY TABLE tmp_sort (id INT PRIMARY KEY, ord INT);
INSERT INTO tmp_sort VALUES (55,1),(6,2),(5,3),(173,4),(176,5),(174,6),(175,7),(177,8);
SELECT h.* FROM cms_hit h
INNER JOIN tmp_sort s ON h.aid = s.id
ORDER BY s.ord;
DROP TEMPORARY TABLE tmp_sort;

效能測試參考 (100萬行表，8 個 ID)

+--------------------+----------+----------+
| 方案               | 執行時間 | 索引使用 |
+--------------------+----------+----------+
| FIELD()            | 12 ms    | range    |
| INSTR()            | 45 ms    | range    |
| CASE WHEN          | 15 ms    | range    |
| 臨時表 JOIN        | 18 ms    | ref      |
+--------------------+----------+----------+

避坑指南

FIELD() 返回 0 表示不在列表，ORDER BY FIELD() 會將不在列表的行排在最前（0 最小），**務必配合 WHERE IN 過濾**
ID 列表超過 1000 時，FIELD()/CASE WHEN SQL 過長，改用臨時表
若頻繁需要此類排序，考慮在表中增加 sort_order 欄位，應用層維護