mysql创建定时器(event)，定时调用存储过程（Procedure）将查询出结果集并批量插入新表

最近用到了mysql event，整理下笔记：

1.开启定时器

查看定时器状态

SHOW VARIABLES LIKE 'event_scheduler';

开始定时器，设置为1和ON都行

SET GLOBAL event_scheduler = 1;
SET GLOBAL event_scheduler = ON;

注：在MySQL命令行里进行设置开始，当重启MySQL后，该设置就会失效。如果想重启后该设置依然有效，那么就在MySQL配置文件my.cnf里设置my.cnf event_scheduler=ON。

2.创建定时器

举个栗子，sql如下：

CREATE EVENT IF NOT EXISTS insert_into_total_day --不存在则创建名为 ‘insert_into_total_day’的任务
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR) --定义执行的时间和时间间隔  每天凌晨一点
ON COMPLETION PRESERVE --事件完成后，该event仍然保留
ENABLE --启用
COMMENT '每天时间上报统计' --event 备注
DO CALL start_total(); --调用 start_total存储过程

语法详解：
CREATE EVENT

[IF NOT EXISTS] -----------------------------------------------*标注1

event_name -----------------------------------------------------*标注2

ON SCHEDULE schedule -----------------------------------*标注3

[ON COMPLETION [NOT] PRESERVE] -----------------*标注4

[ENABLE | DISABLE] -----------------------------------------*标注5

[COMMENT ‘comment’] --------------------------------------*标注6

DO sql_statement ----------------------------------------------*标注7

• 标注1：[IF NOT EXISTS]

使用IF NOT EXISTS，只有在同名event不存在时才创建，否则忽略。建议不使用以保证event创建成功。

• 标注2：event_name

名称最大长度可以是64个字节。名字必须是当前Database中唯一的，同一个数据库不能有同名的event。

使用event常见的工作是创建表、插入数据、删除数据、清空表、删除表。

为了避免命名规范带来的不便，最好让事件名称具有描述整个事件的能力。建议命名规则如下为：动作名称_(INTO/FROM_)表名_TIME，例如：

1. 每月创建(清空/删除)fans表： create(truncate/drop)_table_fans_month；
   

2. 每天从fans表插入(删除)数据：create(truncate/drop)_table_fans_month；
   

• 标注3：ON SCHEDULE

ON SCHEDULE 计划任务，有两种设定计划任务的方式：

1. AT 时间戳，用来完成单次的计划任务。

2. EVERY 时间(单位)的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。
   举个栗子：

#2022-08-25 01:30:00开启事件，以后每天01:30:00执行
EVERY 1 DAY STARTS '2022-08-25 01:30:00'

在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。

在重复的计划任务中，时间(单位)的数量可以是任意非空(Not Null)的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

提示: 其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建议使用这些不标准的时间单位。

• 标注4： [ON COMPLETION [NOT] PRESERVE]

ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。

• 标注5：[ENABLE | DISABLE]参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。

可以用如下命令关闭或开启事件：

ALTER EVENT event_name  ENABLE/DISABLE

• 标注6：[COMMENT ‘comment’]

注释会出现在元数据中，它存储在information_schema表的COMMENT列，最大长度为64个字节。'comment’表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。

• 标注 7: DO sql_statement

DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句，例如：

BEGIN

CREATE TABLE test1;//创建表(需要测试一下)

DROP TABLE test2;//删除表

CALL proc_test1();//调用存储过程

END

使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function 和触发器Trigger 中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：

LOCK TABLES

UNLOCK TABLES

CREATE EVENT

ALTER EVENT

LOAD DATA

3.创建存储过程

-- 如果存在重名的存储过程 先删除后 再创建
DROP PROCEDURE IF EXISTS start_total; -- 注意这里不能加()
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE start_total()；-- 这个是存储过程名称 随便起；
BEGIN
	 -- 需要定义接收游标数据的变量  可以判断select取出的list 是否已经空了
	 DECLARE done BOOLEAN DEFAULT 0;
	 -- Prepare预处理语句
	 -- DECLARE sql VARCHAR(5000);
	 --定义业务变量，注意：变量不能和插入表字段的字段名一样
	 DECLARE v_area_name varchar(64);
	 DECLARE v_total int;

	-- 存储郭过程可以使用预处理，先删除当天的数据，支持重跑。
	-- SET sql ='DELETE FROM test WHERE DATADATE = '''||dataDate||'''';
	-- PREPARE S1 FROM sql;
	-- EXECUTE S1;
	
  	-- 定义游标批量查询  cur1 里面是查询出的结果集list； cursor 是集合的意思
	DECLARE cur1 CURSOR FOR
		SELECT
			sum(*) as v_total,
			area_name as v_area_name
		FROM
			event_business_sub6
		WHERE
			event_del_flag = 0 
			AND TO_DAYS( NOW() ) - TO_DAYS(create_time) <= 1;
	
	-- 定义declare continue handler，这个会根据上下文是否有结果判断是否执行SET notfound = 1  这个不用看  是标准化的 系统会自动判断  如果集合里面空了  自动设置为1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	-- 使用任意一种方式定义都可以
	-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  	-- 打开游标 	使用select取出来的list  必须先open
	OPEN cur1;
 	-- 开始循环批量插入
	REPEAT
		--从list里面再取出一条数据 分别赋值给我们之前事先定义好的变量v_area_name，v_area_name
		FETCH cur1 INTO v_area_name,v_total;
		-- 判断是否读到游标末尾 如果不加这个条件 插入的数据会增多1条
		IF done <1 OR done >1 THEN
			INSERT INTO test VALUES(null,v_area_name,null,v_total,DATE_SUB(curdate(),INTERVAL 1 DAY));
		END IF;
		UNTIL done = 1

	-- 循环结束
	END REPEAT;

  -- 关闭游标
  CLOSE cur1 ;
END $

-- 清空结果表数据
-- TRUNCATE TABLE test;

-- -- 执行存储过程
-- CALL pro_test();

常用命令

查看数据库当前存在的事件

三个sql都可以

SHOW EVENTS;

SELECT * from mysql.event

SELECT * FROM information_schema.EVENTS;

删除已经创建的事件

DROP EVENT IF EXISTS event_name

注：但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除

修改事件(ALTER EVENT)

ALTER EVENT event_name

[ON SCHEDULE schedule]

[RENAME TO new_event_name]

[ON COMPLETION [NOT] PRESERVE]

[COMMENT ‘comment’]

[ENABLE | DISABLE]

[DO sql_statement]