萬盛學電腦網

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

MySQL「Invalid use of group function」錯誤解析:UPDATE 中不可直接用 COUNT

錯誤重現

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;

更簡潔寫法 (無 JOIN)

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 別硬上,語法報錯別慌張。」
copyright © 萬盛學電腦網 all rights reserved