mysql - mysql 中的预定事件

Question

我有两个表。players 其中包含所有玩家的详细信息，players_copy 为空.

我需要用 SQL 编写一个计划的事件脚本，其中 1 分钟后，来自玩家的 10 行被插入到players_copy 中。 2 分钟后，插入 20 行。3 分钟后，插入 30 行，依此类推。我编写了以下脚本

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
ON COMPLETION PRESERVE
DO
   insert into players_copy
 (
  Player_ID,
  Attributes,
 imagePath, 
 Full_Name, 
 Short_Name, 
 Team_Full_Name,
  Team_Short_Name,
  Team_ID,
  Commentry_Name
 ) 
SELECT *
  FROM players
  ORDER BY Team_Full_Name
 limit 10

但是它一遍又一遍地执行相同的 10 行..它不会递增..有人可以帮助我解决如何更新数据的逻辑，以便我达到上述条件，我应该使用存储过程还是随之触发。

Answer 1

尝试如下:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> USE `_`;
Database changed

mysql> DROP TABLE IF EXISTS `players`, `players_copy`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `players` (
    ->   `player_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `full_name` VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `players_copy` (
    ->   `player_id` BIGINT UNSIGNED,
    ->   `full_name` VARCHAR(255),
    ->   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `players` (`full_name`) VALUES
    -> ('Player 1'), ('Player 2'), ('Player 3'), ('Player 4'),
    -> ('Player 5'), ('Player 6'), ('Player 7'), ('Player 8'),
    -> ('Player 9'), ('Player 10'), ('Player 11'), ('Player 12'),
    -> ('Player 13'), ('Player 14'), ('Player 15'), ('Player 16'),
    -> ('Player 17'), ('Player 18'), ('Player 19'), ('Player 20'),
    -> ('Player 21'), ('Player 22'), ('Player 23'), ('Player 24'),
    -> ('Player 25'), ('Player 26'), ('Player 27'), ('Player 28'),
    -> ('Player 29'), ('Player 30'), ('Player 31'), ('Player 32'),
    -> ('Player 33'), ('Player 34'), ('Player 35'), ('Player 36'),
    -> ('Player 37'), ('Player 38'), ('Player 39'), ('Player 40'),
    -> ('Player 41'), ('Player 42'), ('Player 43'), ('Player 44'),
    -> ('Player 45'), ('Player 46'), ('Player 47'), ('Player 48'),
    -> ('Player 49'), ('Player 50'), ('Player 51'), ('Player 52');
Query OK, 52 rows affected (0.00 sec)
Records: 52  Duplicates: 0  Warnings: 0

mysql> DELIMITER //

mysql> DROP EVENT IF EXISTS `test_event_03`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `test_event_03` ON SCHEDULE EVERY 1 MINUTE
    -> STARTS CURRENT_TIMESTAMP
    -> ENDS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
    -> ON COMPLETION PRESERVE
    -> DO
    -> BEGIN
    ->   DECLARE `_starts` DATETIME DEFAULT
    ->     (SELECT `STARTS`
    ->      FROM `information_schema`.`EVENTS`
    ->      WHERE `EVENT_SCHEMA` = '_' AND `EVENT_NAME` = 'test_event_03');
    ->   DECLARE `_range` TINYINT DEFAULT
    ->     (SELECT (TIMESTAMPDIFF(SECOND, `_starts`, CURRENT_TIMESTAMP()) / 60) * 10);
    ->   INSERT INTO `players_copy`
    ->   SELECT `player_id`, `full_name`
    ->   FROM `players`
    ->   LIMIT `_range`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;