用于插入和删除的 MySQL 事件调度程序语法

Question

我想知道你能否告诉我 ff sql 语句有什么问题:

insert into translog 
  select * from transponder_logs where trans_log_id < 150000;
delete from transponder_logs where trans_log_id < 150000

这个语句在sql中运行得很好，但是当我在event scheduler上使用它时，它给了我一个语法错误。 。

错误消息是:

"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 'delete from transponder_logs where trans_log_id < 150000 at line 3"

Answer 1

每当您定义具有多个可执行语句的例程之类的代码时，您必须定义一个自定义DELIMITER。您的代码将与分隔符指令一起发送到服务器。服务器在找到新定义的自定义分隔符之前将代码编译为 block 。

阅读文档内容:
Defining Stored Programs

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. .... The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

我相信您的事件调度程序代码只是按原样执行，而没有定义这样的分隔符。

更改如下:

-- set the new delimiter
DELIMITER //

-- include your event scheduler code block here

-- lastly terminate the code block, with new delimiter
-- so that server starts compiling the code

//

-- now reset the delimiter to default
DELIMITER ;

引用:CREATE EVENT Syntax