UNION 與 ORDER BY 結合時,優先級歧義易導致結果不符預期。MySQL 官方文檔建議顯式括號消除歧義。本文通過實測驗證默認行為、括號語法、LIMIT 交互規則、三種 UNION 變體差異,給出無歧義寫法規範。
SELECT a FROM t1 WHERE a=10
UNION
SELECT a FROM t2 WHERE a=11
ORDER BY a LIMIT 10;
問題:ORDER BY 是作用於 UNION 結果集,還是 第二個 SELECT?
(SELECT a FROM t1 WHERE a=10 AND b=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;
✅ 清楚表達:先 UNION,再全局排序,再 LIMIT。
(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);
⚠️ 關鍵限制:單個 SELECT 中的 ORDER BY 必須配合 LIMIT 才生效,否則優化器會直接忽略(因為 UNION 會重新排序去重)。
無括號寫法:
SELECT a FROM t1 WHERE a=10 AND b=1
UNION
SELECT a FROM t2 WHERE a=11 AND b=2
ORDER BY a LIMIT 10;
實測結果:等同於場景 1,即先 UNION 去重,再全局 ORDER BY,再 LIMIT。但依賴隱式行為屬於「未定義行為」,版本升級可能變更。
| 語法 | 去重 | 性能 | 用途 |
|---|---|---|---|
UNION / UNION DISTINCT | ✅ 全局去重(排序+唯一) | 較慢 | 業務要求唯一結果集 |
UNION ALL | ❌ 保留所有行(含重複) | 極快(無排序去重) | 日誌匯總、已知無重複、性能敏感 |
(SELECT ...) UNION (SELECT ...),消除歧義、提升可讀性ORDER BY;需分片額度 → 內層 ORDER BY ... LIMITSELECT * FROM (
(SELECT id, name FROM users WHERE status=1)
UNION ALL
(SELECT id, name FROM archive_users WHERE status=1)
) AS u
ORDER BY u.name LIMIT 20;
遵循顯式括號規範,讓 UNION 行為可預期、可維護、可演進。