UPDATE v9_keyword AS a
LEFT JOIN v9_keyword_data AS b ON a.id = b.tagid
SET a.videonum = COUNT(b.tagid) -- 錯誤寫法
WHERE a.id = b.tagid;
-- ERROR 1111 (HY000): Invalid use of group function
MySQL 禁止在 UPDATE ... SET 子句中直接使用聚合函數 (COUNT, SUM, AVG 等)。聚合屬於查詢層面,需在子查詢中完成。
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;
UPDATE v9_keyword a
SET a.videonum = (
SELECT COUNT(*) FROM v9_keyword_data b WHERE b.tagid = a.id
);
a 執行一次 (相關子查詢)。v9_keyword_data(tagid) 時極快。UPDATE v9_keyword a
SET
videonum = (SELECT COUNT(*) FROM v9_keyword_data WHERE tagid = a.id),
last_used = (SELECT MAX(created_at) FROM v9_keyword_data WHERE tagid = a.id);
-- 使用游標分批,避免長事務鎖表
DELIMITER $$
CREATE PROCEDURE batch_update_stats()
BEGIN
DECLARE v_id INT;
DECLARE done INT DEFAULT 0;
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;
-- 每 500 行提交
IF (SELECT COUNT(*) FROM v9_keyword WHERE id <= v_id) % 500 = 0 THEN
COMMIT;
END IF;
END LOOP;
CLOSE cur;
COMMIT;
END$$
DELIMITER ;
「UPDATE 要聚合,子查詢來幫忙;COUNT SUM AVG 別硬上,語法報錯別慌張。」