萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 集合操作 UNION 與 UNION ALL 完整指南

集合操作是 SQL 中垂直合併結果集的核心手法,與水平合併的 JOIN 形成互補。MySQL 支援 UNION DISTINCT(預設)與 UNION ALL 兩種模式。本文通過實戰範例詳細解析其語法、行為差異、效能特性與最佳實踐。

核心概念:垂直操作

JOIN 是「水平拼接」——合併欄位;集合操作是「垂直堆疊」——合併行。兩輸入必須:

  • 欄位數量相同
  • 對應位置資料類型相容(MySQL 會隱式轉換)
  • 結果欄位名稱以第一個 SELECT 為準

測試資料準備

CREATE TABLE table1 (
  aid INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(20),
  tag VARCHAR(10),
  PRIMARY KEY (aid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE table2 (
  bid INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(20),
  tag VARCHAR(10),
  PRIMARY KEY (bid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO table1 (title, tag) VALUES 
('article1', 'MySQL'), ('article2', 'PHP'), ('article3', 'CPP');

INSERT INTO table2 (title, tag) VALUES 
('article1', 'MySQL'), ('article2', 'CPP'), ('article3', 'C');

UNION DISTINCT(預設行為)

合併結果並去重(完全相同的行只保留一條):

mysql> (SELECT * FROM table1) UNION (SELECT * FROM table2);
+-----+----------+-------+
| aid | title    | tag   |
+-----+----------+-------+
| 1   | article1 | MySQL |
| 2   | article2 | PHP   |
| 3   | article3 | CPP   |
| 2   | article2 | CPP   |
| 3   | article3 | C     |
+-----+----------+-------+
5 rows in set

去重邏輯:所有選取欄位值完全相同才視為重複。上例中 (1, 'article1', 'MySQL') 只出現一次。

MySQL 內部實現機制

  1. 創建內部臨時表
  2. 對所有欄位建立唯一索引
  3. 將兩輸入結果插入臨時表(重複鍵衝突自動忽略)
  4. 返回臨時表內容

⚠️ 效能含義:唯一索引維護帶來額外 CPU/記憶體開銷,大結果集時明顯。

UNION ALL:保留所有行

不去重,直接堆疊:

mysql> (SELECT * FROM table1) UNION ALL (SELECT * FROM table2);
+-----+----------+-------+
| aid | title    | tag   |
+-----+----------+-------+
| 1   | article1 | MySQL |
| 2   | article2 | PHP   |
| 3   | article3 | CPP   |
| 1   | article1 | MySQL |
| 2   | article2 | CPP   |
| 3   | article3 | C     |
+-----+----------+-------+
6 rows in set

重複行 (1, 'article1', 'MySQL') 完整保留。

效能對比與選擇策略

維度UNION DISTINCTUNION ALL
去重是(全欄位比較)否
臨時表索引唯一索引(全欄位)無索引
記憶體/CPU較高極低
適用場景業務要求去重、不確定有無重複確知無重複、或業務允許重複、效能敏感

進階用法:多表聯合與排序

-- 多表 UNION 並全局排序
(SELECT id, name, created_at FROM users_2023 WHERE status=1)
UNION ALL
(SELECT id, name, created_at FROM users_2024 WHERE status=1)
ORDER BY created_at DESC LIMIT 20;

-- 搭配 LIMIT 下推(MySQL 8.0+ 優化器支援)
(SELECT * FROM t1 WHERE c > 100 ORDER BY c LIMIT 10)
UNION ALL
(SELECT * FROM t2 WHERE c > 100 ORDER BY c LIMIT 10)
ORDER BY c LIMIT 10;

常見陷阱

  • 欄位順序不一致:SELECT a,b FROM t1 UNION SELECT b,a FROM t2 會導致語義錯誤,務必顯式列出欄位名
  • 資料類型隱式轉換:INT UNION VARCHAR 可能導致索引失效或精度損失,建議 CAST 統一
  • ORDER BY 位置:只能在最後一個 SELECT 後或整個 UNION 後,不能在中間

總結

UNION ALL 是預設首選——零開銷、可預測效能。只有在業務明確要求去重且無法在應用層處理時,才使用 UNION (DISTINCT)。永遠顯式指定欄位列表,避免 SELECT * 帶來的 Schema 變更風險。

copyright © 萬盛學電腦網 all rights reserved