mysql - 当我尝试将 SQL 查询添加到事件时，它不起作用

Question

我有这个查询:

SET @rank = 0;
UPDATE t1 SET rank = (@rank := @rank + 1)
ORDER BY balance DESC;

我想创建一个事件，每 x 分钟执行一次此查询，但我不断收到此错误:

The following query has failed: "CREATE DEFINER=root@localhost EVENT updaterank ON SCHEDULE EVERY 5 SECOND STARTS '2019-09-17 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SET @rank = 0; UPDATE t1 SET rank = (@rank := @rank + 1) ORDER BY balance DESC;" MySQL said:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE t1 SET rank = (@rank := @rank + 1) ORDER BY balance DESC' at line 3

Answer 1

当我查看错误时，它发现您在 MySQL 用户帐户/主机名中使用了反引号，这些反引号应该像 'root'@'localhost' 一样用单引号引起来。我会使用 DEFINER = CURRENT_USER() 代替，但一定要从正确的帐户运行它..

CREATE DEFINER = CURRENT_USER() EVENT `updaterank`
ON SCHEDULE EVERY 5 SECOND STARTS '2019-09-17 00:00:00' ON COMPLETION NOT PRESERVE ENABLE
DO
  SET 
   @rank = 0;
   UPDATE t1 SET rank = (@rank := @rank + 1) ORDER BY balance DESC;

参见demo

或者

CREATE DEFINER = CURRENT_USER() EVENT `updaterank`
ON SCHEDULE EVERY 5 SECOND STARTS '2019-09-17 00:00:00' ON COMPLETION NOT PRESERVE ENABLE
DO
  UPDATE t1 CROSS JOIN (SELECT @rank := 0) AS init  SET rank = (@rank := @rank + 1) ORDER BY balance DESC;