MySQL创建AFTER INSERT触发器引用新插入行变量时报语法错误

问题根源 #

你遇到的语法报错是因为MySQL默认语句分隔符为;，触发器BEGIN...END块内部的UPDATE语句末尾的;会被MySQL提前判定为整个CREATE TRIGGER语句的结束，导致后续内容语法解析失败。

解决方法 #

创建触发器前临时修改语句分隔符，触发器定义完成后再改回默认分隔符即可，正确代码如下：

-- 临时修改语句分隔符为//，避免内部的;提前截断触发器定义
DELIMITER //
CREATE TRIGGER update_post_comments
AFTER INSERT 
ON `comment` FOR EACH ROW 
BEGIN
    UPDATE `post` 
    SET total_comments = total_comments + 1
    WHERE post_id = NEW.post_id;
END //
-- 恢复默认语句分隔符为;
DELIMITER ;

额外优化建议 #

如果你的post表total_comments字段默认值为NULL，可将更新语句修改为以下写法，避免首次计数时出现NULL值错误：

SET total_comments = IFNULL(total_comments, 0) + 1

内容的提问来源于stack exchange，提问作者Ann