phpmyadmin|如何创建事件做2个动作

2022-01-05 00:00:00 mysql phpmyadmin

我想创建一个可以执行两个操作的事件，但我不知道如何操作.这是查询:

I want to create an event that can do two actions but I don't know how. Here is the query:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO SET @p=1
UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC

我也尝试在操作之间写入 AND ，但它仍然写入错误.

I tried also to write AND between the actions and it still writes an error.

错误是:

#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 'UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC' at line 2

推荐答案

您可以将事件的主体包装在 BEGIN ... END 复合语句块:

You can wrap the body of your event in a BEGIN ... END compound statement block:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO BEGIN
  SET @p=1;
  UPDATE users SET rate = (@p:=@p+1) ORDER BY power DESC;
END

请注意，每个语句都必须以分号结尾，因此您必须将客户端配置为使用不同的语句分隔符，以便它不会认为遇到的第一个分号是 CREATE EVENT 命令(如何执行此操作取决于您的客户端，但在 MySQL 命令行工具中，你可以使用 DELIMITER command 并且在 phpMyAdmin 中你可以在 SQL 输入框下方设置分隔符).

Note that each statement must be terminated with a semicolon, so you must configure your client to use a different statement delimiter in order that it does not think the first encountered semicolon is the end of the CREATE EVENT command (how to do this will depend on your client, but in the MySQL command line tool, you can use the DELIMITER command and in phpMyAdmin you can set the delimiter below the SQL input box).

或者，在这种情况下，您可以使用多表 UPDATE 语法来执行变量初始化，这样您只需要一个简单的语句:

Alternatively, in this case you can use the multiple-table UPDATE syntax to perform variable initialisation such that you only require a single simple statement:

CREATE EVENT rate ON SCHEDULE EVERY 24 HOUR STARTS '2011-12-01 20:00:00' DO
  UPDATE   users, (SELECT @p:=0) init
  SET      users.rate = (@p:=@p+1)
  ORDER BY users.power DESC

相关文章