一 开启事件配置
1.1 开启事件配置
show variables like '%event_sche%'
1.临时会话的修改,注意mysql服务重新启动将失效
set global event_scheduler=1
2.在/etc/my.cnf配置文件中进行配置:event_scheduler=1,然后重新启动mysql服务
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
default-character-set=utf8
[mysqld]
# 一般配置选项
basedir = /usr/local/mysql-5.7.29
datadir = /usr/local/mysql-5.7.29/data
default-time-zone=+08:00
event_scheduler=1
port = 3306
character-set-server=utf8
default_storage_engine = InnoDB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=true
再次查看 事件是否开启,如图所示:
二 案例演示
1.新建存储过程
CREATE PROCEDURE test_d2()
BEGIN
DROP TABLE IF EXISTS test_ddd;
/*************************************/
CREATE table test_ddd AS SELECT NOW()
;
END
2.设置定时计划
1.每5秒执行一次
DROP EVENT IF EXISTS hh;
create event hh
ON SCHEDULE EVERY 5 SECOND STARTS TIMESTAMP '2022-07-22 01:00:00'
ON COMPLETION PRESERVE
DO
begin
CALL test_d2();
end
2.设置指定时刻执行
drop event if exists t_event_name ;
create event t_event_name
on schedule at '2022-07-22 09:07:00' on completion preserve
do call test_d2();