mysql 定时任务 EVENT

show variables like 'event_scheduler';

set global event_scheduler = on; -- 登录root 进行设置

CREATE EVENT gmp_remainDays_event
ON SCHEDULE
EVERY 1 Minute
ON COMPLETION PRESERVE
DO
BEGIN
update gmp_check_info set remain_days= datediff(now(),begin_date);
update gmp_verify_info set remain_days= datediff(now(),begin_date);
update gmp_maintain_info set remain_days= datediff(now(),begin_date);
end;

use gmp;
show events;
DROP EVENT IF EXISTS remainDays_check_even1;

 ----------------------

[root@VM-4-3-centos ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27620
Server version: 5.7.40-log Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> set global event_scheduler = on;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> drop event if exists remainDays_check_event;
ERROR 1046 (3D000): No database selected
mysql> select gmp;
ERROR 1054 (42S22): Unknown column 'gmp' in 'field list'
mysql> use gmp;
Database changed
mysql> drop event if exists remainDays_check_event;
Query OK, 0 rows affected (0.00 sec)

mysql> show events;
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| gmp | remainDays_check_event1 | gmpuser@% | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-07-19 15:15:48 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| gmp | remainDays_check_event2 | gmpuser@% | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-07-19 15:26:24 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

mysql> drop event if exists remainDays_check_event1;
Query OK, 0 rows affected (0.01 sec)

mysql> show events;
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| gmp | remainDays_check_event2 | gmpuser@% | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-07-19 15:26:24 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-----+-------------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> drop event if exists remainDays_check_event2;
Query OK, 0 rows affected (0.00 sec)

mysql> show events;
+-----+------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----+------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| gmp | check_remainDays | gmpuser@% | SYSTEM | RECURRING | NULL | 1 | MINUTE | 2024-07-19 15:35:40 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-----+------------------+-----------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> drop event if exists check_remainDays;
Query OK, 0 rows affected (0.00 sec)

mysql> show events;
Empty set (0.00 sec)

mysql>