mysql - 创建事件时出现错误#1064

Question

我有一张 table :

CREATE TABLE test_time (`id` int(11) not null, `num` int(11) not null, PRIMARYKEY(`id`);

之后，我创建一个事件:

CREATE
EVENT testEvent
ON
SCHEDULE EVERY 1 SECOND 
DO 
BEGIN
UPDATE
    test_time
SET
    num = num + 1 
WHERE 
    id = 1;

UPDATE
    test_time
SET
    num = num + 1 
WHERE 
    id = 2;
END

MySQL 说:(注意:第 12 行是 id = 1;)

#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 12

为什么会出现错误以及如何修复它？

Answer 1

在创建 CREATE 事件之前，您需要更改分隔符，该事件将在第一个分号处停止。完成后，您需要将其改回来。

CREATE TABLE if not exists test_time 
   (`id` int(11) not null, `num` int(11) not null, PRIMARY KEY(`id`));

drop event if exists testEvent;

Delimiter $$                 /* New delimiter set here */
CREATE EVENT testEvent
  ON SCHEDULE EVERY 1 SECOND 
DO 
  BEGIN
    UPDATE test_time SET num = num + 1 WHERE  id = 1;  /* old delimiter not actioned */
    UPDATE test_time SET num = num + 1 WHERE  id = 2;
  END $$                                               /* New delimiter to end CREATE */

delimiter ;            /* Reset delimiter */