MySQL创建存储过程,事件定时执行
创建存储过程
-
Procedure格式
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE procedure_name([[IN |OUT |INOUT ] 参数名 数据类形...]) BEGIN [characteristic ...] routine_body END [end_label]
-
IN 输入参数:调用者项存储过程传入值
OUT 输出参数: 过程向调用者传出值 \
INOUT 输入输出参数 - 变量
-
-
样例
复制表,并同步数据
create procedure data_sync() begin -- 变量定义 declare sourceTableName varchar(50); declare tableName varchar(50); select 'td_counter' into sourceTableName; select concat(sourceTableName, '_', date_format(now(), '%Y_%m_%d')) into tableName; -- 变量赋值 set @stmt = concat('create table ', tableName, ' like ', sourceTableName, ';'); prepare stmt from @stmt; execute stmt; set @copyData = concat('insert into ', tableName, ' select * from ', sourceTableName, ';'); prepare copyData from @copyData; execute copyData; end;
-
存储过程调用
call data_sync();
创建定时执行事件
-
创建事件
create event event_data_sync on schedule -- 每天执行 every 1 day -- 不过期 on completion preserve -- 启用 enable comment '数据同步' -- 调用存储过程 do call data_sync();
-
查看事件
show events;
-
删除事件
drop event even_name;
-
开启事件调度开关
set global event_scheduler = 1;
开启后所有事件才能生效 -
禁用事件
alter even even_name disable | enable;