問題場景

需按指定 ID 順序 (30, 20, 80, 40) 回傳記錄，但 WHERE id IN (30,20,80,40) 預設按主鍵遞增排序（20,30,40,80）。

解決方案：FIELD() 函數

SELECT * FROM my_table 
WHERE id IN (30, 20, 80, 40) 
ORDER BY FIELD(id, 30, 20, 80, 40);

原理

FIELD(str, val1, val2, ...) 回傳 str 在列表中的位置（1-based），找不到回傳 0。ORDER BY 依此位置排序。

效能陷阱：Using filesort

EXPLAIN SELECT * FROM my_table 
WHERE id IN (30,20,80,40) ORDER BY FIELD(id, 30,20,80,40);
-- type: range / ref
-- Extra: Using where; Using filesort

Using filesort 表示需在記憶體/磁碟排序，**無法使用索引排序**，資料量大時效能差。

優化方案

方案一：應用層排序（推薦，效能最好）

# PHP 範例
$ids = [30, 20, 80, 40];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT * FROM my_table WHERE id IN ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 應用層按 $ids 順序重新排序
$idToRow = array_column($rows, null, 'id');
$sorted = [];
foreach ($ids as $id) {
    if (isset($idToRow[$id])) $sorted[] = $idToRow[$id];
}

優點：資料庫無 filesort，網路傳輸同樣，記憶體排序極快。

方案二：臨時表/衍生表 JOIN（純 SQL）

SELECT t.* 
FROM my_table t
JOIN (
    SELECT 1 AS sort_order, 30 AS id UNION ALL
    SELECT 2, 20 UNION ALL
    SELECT 3, 80 UNION ALL
    SELECT 4, 40
) o ON t.id = o.id
ORDER BY o.sort_order;

優點：可用索引 JOIN，無 filesort。缺點：SQL 冗長，動態構建麻煩。

方案三：MySQL 8.0+ CTE + VALUES

WITH ordered_ids (sort_order, id) AS (
    VALUES ROW(1,30), ROW(2,20), ROW(3,80), ROW(4,40)
)
SELECT t.* FROM my_table t
JOIN ordered_ids o ON t.id = o.id
ORDER BY o.sort_order;

適用場景選擇表

資料量	頻率	推薦方案
< 1 萬	低頻	FIELD() 簡單直接
> 1 萬	高頻/核心業務	應用層排序
大量 ID (百個+)	任何	臨時表/VALUES JOIN
需分頁	任何	應用層排序後分頁

延伸：FIELD() 其他用法

-- 自定義狀態排序：待處理(1) > 處理中(2) > 已完成(3) > 已取消(4)
ORDER BY FIELD(status, 'pending', 'processing', 'completed', 'cancelled')

-- 字串欄位同樣適用
ORDER BY FIELD(category, 'VIP', 'Normal', 'Trial')