mysql - 添加触发器的正确语法

代码：

# Triggers for table Likes->News

CREATE TRIGGER `TriggerUpdateNewsAfterInsertLikes` AFTER INSERT ON `Likes`
FOR EACH ROW
BEGIN
UPDATE `News` SET
`CountUpLikes` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `Type` = 'up'),
`CountDownLikes` = (SELECT COUNT (*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `Type` = 'down'),
`CountFavorites` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `IsFavorite` = 'yes');
END;

当我想在phpmyadmin中添加触发器时，得到错误：

#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near '*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn`
AND `Type` = 'down'),
`CountFav' at line 8

如果我使用id而不是*则会得到错误：

#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near '' at line 9

请告诉我加触发器有多正确？

最佳答案

听起来难以置信，在mysql中，不能在count和(*)之间有空格：

select count(*) -- OK
select count (*) -- syntax error!

在您的例子中，它在第二个子查询中。更改为：

...
`CountDownLikes` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `Type` = 'down'),
...

这同样适用于cast(...)，这是一个语法错误：cast (col as unsigned)（注意cast和(之间的空格）。我可以告诉你我花了大约一个小时去寻找那颗小宝石。
编辑；
另一个错误可能是由于没有更改分隔符，如果过程中包含分号，则需要更改分隔符：

DELIMITER //

CREATE TRIGGER `TriggerUpdateNewsAfterInsertLikes` AFTER INSERT ON `Likes`
FOR EACH ROW
BEGIN
UPDATE `News` SET
`CountUpLikes` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `Type` = 'up'),
`CountDownLikes` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `Type` = 'down'),
`CountFavorites` = (SELECT COUNT(*) FROM `Likes` WHERE `NewsIdn` = NEW.`NewsIdn` AND `IsFavorite` = 'yes');
END;//

DELIMITER ;

关于mysql - 添加触发器的正确语法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21251050/

加触发器

mysql - 添加触发器的正确​​语法

mysql - 添加触发器的正确语法