mysql - MySQL 每 1 天执行一次存储过程

Question

我必须每天在特定时间(比如 00:00:00)从我的数据库中清空我的一个表。我在程序中有这个:

    CREATE DEFINER=`root`@`%` PROCEDURE `delete_day`()
    BEGIN
       TRUNCATE TABLE qmsos.module_queuemanagement_queue;
    END

我尝试制作一个事件，每天调用并重复这样的事件:

CREATE 
    EVENT `delete_queue_daily`
    ON SCHEDULE EVERY 1 DAY STARTS '2017-05-12 00:00:00' 
    ON COMPLETION PRESERVE
    DO CALL delete_day();

但它不起作用。我尝试只在特定时间执行一次并且成功了，但如果我想每天都运行它就不行了。

Answer 1

我无法重现问题。

在基本测试中，存储过程每 10 秒执行一次:

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

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

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> USE `qmsos`;
Database changed

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

mysql> DROP PROCEDURE IF EXISTS `delete_day`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `module_queuemanagement_queue`,
    ->                      `audit_log`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `module_queuemanagement_queue` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `module_queuemanagement_queue`
    ->   (`id`)
    -> VALUES
    ->   (NULL), (NULL), (NULL), (NULL), (NULL); 
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `audit_log` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `delete_queue_daily`
    ->   ON SCHEDULE EVERY 10 SECOND STARTS '2017-05-12 00:00:00' 
    ->   ON COMPLETION PRESERVE
    ->   DO CALL `delete_day`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE `delete_day`()
    -> BEGIN
    ->   INSERT INTO `audit_log` (`id`) VALUES (NULL);
    ->   TRUNCATE TABLE `module_queuemanagement_queue`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: qmsos
                Name: delete_queue_daily
             Definer: user@127.0.1.1
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2017-05-12 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql> SELECT `id`, `created_at`
    -> FROM `audit_log`;
Empty set (0.00 sec)

mysql> SELECT `id`
    -> FROM `module_queuemanagement_queue`;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT `id`, `created_at` FROM `audit_log`;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2017-05-12 00:00:10 |
+----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT `id` FROM `module_queuemanagement_queue`;
Empty set (0.00 sec)