我有以下SQL代码：
CREATE EVENT `update_statistics`
ON SCHEDULE EVERY 1 DAY STARTS '2015-08-24 02:00:00'
ON COMPLETION PRESERVE
DO BEGIN
UPDATE statistics
SET km_traveled = (SELECT sum(km)
FROM archived_trips),
passengers_driven = (SELECT sum(passengers)
FROM archived_trips),
trips_taken = (SELECT count(*)
FROM archived_trips)
WHERE id = 1;
UPDATE statistics
SET co_saved = ((SELECT km_traveled
FROM statistics) * 0.215 * (SELECT passengers_driven
FROM statistics))
WHERE id = 1;
END;
当我通过PhpStorm中的SQL控制台运行它时-它运行良好并且计划的任务有效，依此类推。
但是，如果我尝试直接在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 '' at line 13
第13行是WHERE id=1;。老实说，我没有看到查询的任何语法问题。有什么建议？
查询的定界符为“;” 和 ';' 在存储过程中也使用了“”，导致查询失败。
DELIMITER //在语句之前添加，然后//以解决此问题。
DELIMITER //
CREATE EVENT `update_statistics`
ON SCHEDULE EVERY 1 DAY STARTS '2015-08-24 02:00:00'
ON COMPLETION PRESERVE
DO BEGIN
UPDATE statistics
SET km_traveled = (SELECT sum(km)
FROM archived_trips),
passengers_driven = (SELECT sum(passengers)
FROM archived_trips),
trips_taken = (SELECT count(*)
FROM archived_trips)
WHERE id = 1;
UPDATE statistics
SET co_saved = ((SELECT km_traveled
FROM statistics) * 0.215 * (SELECT passengers_driven
FROM statistics))
WHERE id = 1;
END; //
DELIMITER ;
使用PHPstorm，可能会禁用一次发送多个查询，从而导致MySQL忽略定界符。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句