萬盛學電腦網

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

MySQL 關聯表 UPDATE 實戰:LEFT JOIN + 子查詢實現跨表統計更新

場景

表 v9_keyword (主表) 有欄位 videonum,需統計關聯表 v9_keyword_data 中各 tagid 出現次數並回寫。

表結構

v9_keyword: id (PK), videonum
v9_keyword_data: id, tagid (FK -> v9_keyword.id), ...

核心 SQL

UPDATE v9_keyword AS a
LEFT JOIN v9_keyword_data AS b ON a.id = b.tagid
SET a.videonum = (
  SELECT COUNT(*) FROM v9_keyword_data WHERE tagid = a.id
)
WHERE a.id = b.tagid;

關鍵點解析

  1. LEFT JOIN ... ON a.id = b.tagid:將關聯行連接,未關聯行 b.* 為 NULL。
  2. WHERE a.id = b.tagid:過濾掉無關聯行 (轉為內連接語義),避免更新無關鍵字的 videonum=0 (視業務而定)。
  3. SET a.videonum = (子查詢):不能直接用 COUNT(b.tagid),因聚合函數不允許在 UPDATE SET 中直接使用 (會報 Invalid use of group function),必須用標量子查詢。

等價寫法 (無 JOIN,純子查詢)

UPDATE v9_keyword a
SET a.videonum = (
  SELECT COUNT(*) FROM v9_keyword_data b WHERE b.tagid = a.id
);

效能相近,語意更清晰,推薦此寫法。

大表優化版 (批次提交)

DELIMITER $$
CREATE PROCEDURE batch_update_videonum()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE cur CURSOR FOR SELECT id FROM v9_keyword;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id;
    IF done THEN LEAVE read_loop; END IF;

    UPDATE v9_keyword
    SET videonum = (SELECT COUNT(*) FROM v9_keyword_data WHERE tagid = v_id)
    WHERE id = v_id;

    -- 每 1000 行提交一次,避免長事務
    IF (SELECT COUNT(*) FROM v9_keyword WHERE id <= v_id) % 1000 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  CLOSE cur;
  COMMIT;
END$$
DELIMITER ;

避坑指南

  • 聚合函數不可直接用在 SET → 用標量子查詢。
  • LEFT JOIN + WHERE 等於 INNER JOIN,若需保留無關聯行設為 0,移除 WHERE 或用 COALESCE((SELECT...),0)。
  • 事務控制:大量更新分批提交,避免 Undo Log 膨脹與鎖升級。
  • 索引支援:v9_keyword_data(tagid) 必須有索引,否則子查詢全表掃描極慢。
copyright © 萬盛學電腦網 all rights reserved