萬盛學電腦網

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

MySQL EXISTS 子查詢應用:防止重複插入與集合運算實戰

EXISTS / NOT EXISTS 核心語義

  • `EXISTS (subquery)`:子查詢**至少返回一行** → `TRUE`。
  • `NOT EXISTS (subquery)`:子查詢**返回零行** → `TRUE`。
  • 子查詢 `SELECT` 列表無關緊要 (常用 `SELECT 1` 或 `SELECT *`),只關心**是否存在行**。
  • 相關子查詢:子查詢引用外部查詢表別名,逐行評估。

集合論視角

  • 交集:`SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id)`
  • 差集 (A - B):`SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id)`

防止重複插入:經典模式

場景:向 `clients` 表導入 `suppliers` 數據,避免主鍵衝突

INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers s
WHERE NOT EXISTS (
    SELECT 1 FROM clients c WHERE c.client_id = s.supplier_id
);

原理: 對每一行 `suppliers`,檢查 `clients` 是否已存在相同 `client_id`。不存在則插入。原子性由單條 SQL 保證 (無競態)。

完整演示:書籍-作者關聯

建表與數據

CREATE TABLE books (
    bookid SMALLINT NOT NULL PRIMARY KEY,
    booktitle VARCHAR(60) NOT NULL,
    copyright YEAR NOT NULL
) ENGINE=InnoDB;

INSERT INTO books VALUES
(12786, 'Java', 1934), (13331, 'MySQL', 1919), (14356, 'PHP教程', 1966),
(15729, 'Perl', 1932), (16284, 'Oracle', 1996), (17695, 'PL/SQL', 1980),
(19264, 'JavaScript', 1992), (19354, 'www.java2s.com', 1993);

CREATE TABLE authors (
    authid SMALLINT NOT NULL PRIMARY KEY,
    authfn VARCHAR(20), authmn VARCHAR(20), authln VARCHAR(20)
) ENGINE=InnoDB;

INSERT INTO authors VALUES
(1006, 'H', 'S.', 'T'), (1007, 'J', 'C', 'O'), (1008, 'B', NULL, 'E'),
(1009, 'R', 'M', 'R'), (1010, 'J', 'K', 'T'), (1011, 'J', 'G.', 'N'),
(1012, 'A', NULL, 'P'), (1013, 'A', NULL, 'W'), (1014, 'N', NULL, 'A');

CREATE TABLE authorbook (
    authid SMALLINT NOT NULL,
    bookid SMALLINT NOT NULL,
    PRIMARY KEY (authid, bookid),
    FOREIGN KEY (authid) REFERENCES authors(authid),
    FOREIGN KEY (bookid) REFERENCES books(bookid)
) ENGINE=InnoDB;

INSERT INTO authorbook VALUES
(1006, 14356), (1008, 15729), (1009, 12786), (1010, 17695),
(1011, 15729), (1012, 19264), (1012, 19354), (1014, 16284);

查詢:找出「沒有作者」的書籍 (差集:Books - AuthorBook)

SELECT bookid, booktitle
FROM books b
WHERE NOT EXISTS (
    SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid
)
ORDER BY booktitle;
-- 結果:只有 bookid=13331 'MySQL' (無作者關聯)

查詢:找出「有作者」的書籍 (交集)

SELECT b.bookid, b.booktitle
FROM books b
WHERE EXISTS (
    SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid
)
ORDER BY b.booktitle;

查詢:找出「沒寫過書」的作者 (差集:Authors - AuthorBook)

SELECT a.authid, a.authfn, a.authln
FROM authors a
WHERE NOT EXISTS (
    SELECT 1 FROM authorbook ab WHERE ab.authid = a.authid
);
-- 結果:authid 1007, 1013 (J.C.O 和 A.W)

EXISTS vs IN / JOIN 效能對比

操作EXISTSINJOIN
語義存在性檢查 (半連接)集合成員檢查連接後過濾
NULL 處理安全 (忽略 NULL)**陷阱**:`NOT IN (含 NULL)` 永遠為空需額外條件
子查詢優化優化器常轉為 Semi-Join同 EXISTS可能產生重複行需 DISTINCT
適用場景**防重複插入、檢查存在性、差集**值列表較小、靜態需要獲取關聯表列數據

NOT IN 陷阱實例

-- 危險!若 subquery 返回含 NULL,整個 NOT IN 結果為空集
SELECT * FROM books WHERE bookid NOT IN (SELECT bookid FROM authorbook WHERE authid = 1008);
-- 如果 authorbook 有 NULL bookid (雖然 PK 不允許),NOT IN 失效

-- 安全寫法:NOT EXISTS 或 NOT IN 加非空條件
SELECT * FROM books WHERE bookid NOT IN (SELECT bookid FROM authorbook WHERE authid = 1008 AND bookid IS NOT NULL);
-- 或
SELECT * FROM books b WHERE NOT EXISTS (SELECT 1 FROM authorbook ab WHERE ab.bookid = b.bookid AND ab.authid = 1008);

進階應用:條件插入/更新 (Upsert 替代)

-- MySQL 8.0+ 推薦 ON DUPLICATE KEY UPDATE 或 INSERT ... ON CONFLICT (PostgreSQL)
-- 僅用標準 SQL (如較老版本) 時:
INSERT INTO stats (date, cnt)
SELECT '2023-10-01', 1
WHERE NOT EXISTS (SELECT 1 FROM stats WHERE date = '2023-10-01');

-- 或配合觸發器/存儲過程實現更複雜邏輯

最佳實踐總結

  1. 防重複插入首選 `INSERT ... WHERE NOT EXISTS`:單語句原子、語義清晰、無 `NOT IN` NULL 陷阱。
  2. 子查詢用 `SELECT 1`**:比 `SELECT *` 略快 (避免元數據查找),習慣寫法。
  3. 關聯子查詢確保有索引**:`authorbook(bookid)`、`authorbook(authid)` 等外鍵列必須有索引 (FK 自動建)。
  4. 大數據量考慮 `LEFT JOIN ... WHERE IS NULL`**:某些版本/優化器下比 `NOT EXISTS` 快,需 `EXPLAIN` 對比。
  5. 避免在高併發熱點表長事務中用**:鎖競爭,考慮應用層去重 (Redis Set) + 異步入庫。
copyright © 萬盛學電腦網 all rights reserved