表 v9_keyword (主表) 有欄位 videonum,需統計關聯表 v9_keyword_data 中各 tagid 出現次數並回寫。
v9_keyword: id (PK), videonum
v9_keyword_data: id, tagid (FK -> v9_keyword.id), ...
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;
LEFT JOIN ... ON a.id = b.tagid:將關聯行連接,未關聯行 b.* 為 NULL。WHERE a.id = b.tagid:過濾掉無關聯行 (轉為內連接語義),避免更新無關鍵字的 videonum=0 (視業務而定)。SET a.videonum = (子查詢):不能直接用 COUNT(b.tagid),因聚合函數不允許在 UPDATE SET 中直接使用 (會報 Invalid use of group function),必須用標量子查詢。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 ;
COALESCE((SELECT...),0)。v9_keyword_data(tagid) 必須有索引,否則子查詢全表掃描極慢。