MySQL存储过程+定时器，实现定时查询一张表，将得到的结果插入一张新表

需求分析

需要定时查询现有的一张表，然后将查询结果插入到另一张表。
查询的表结构（表名：mouse_tbl）如下图

查询结果要插入的表(data_time)的结构如下：

我们的需求是将表mouse_tbl中所有数据对应的用户id、日期、时间的小时查询出来，然后插入到data_time表中。
我们实际的需求是想要看到用户在那个日期以及该日期下哪个时间（小时）有数据记录。

解决方案

-- 创建一个存储过程，命名为writeTime
create procedure writeTime()
BEGIN
insert into data_time(`user_id`,`date`,`hour`) select user_id,date,left(time,2) as hour from keyboard_tbl group by user_id,date,hour;
end;

-- 创建一个定时事件命名为eventJob，该事件调用writeTime存储过程，并定义事件执行的频率为从现在开始每30min执行一次
create event if not exists eventJob
on schedule every 30 MINUTE STARTS NOW()
on completion PRESERVE
do call writeTime();

-- 启动定时器
SET GLOBAL event_scheduler = 1;
-- 启动事件 eventJob
ALTER EVENT eventJob ON  COMPLETION PRESERVE ENABLE;

如果后续想关闭定时器和事件可以参考下面的设置

-- 停止定时器
SET GLOBAL event_scheduler = 0;  
-- 关闭事件
ALTER EVENT eventJob ON  COMPLETION PRESERVE DISABLE;  

存储过程相关基本操作

• 存储过程：存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
• 存储过程的优点：

1. 封装性
2. 增强SQL语句的功能和灵活性
3. 可减少网络流量
4. 高性能
5. 提高数据库的安全性和数据的完整性

• 自定义语句结束符号
  在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。
  然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
  语法格式如下：

DELIMITER $$

使用举例

DELIMITER $$ 
DROP PROCEDURE IF EXISTS e_test $$ 
CREATE PROCEDURE e_test() 
BEGIN 
INSERT INTO t VALUES('1'); 
END $$ 
DELIMITER ; 

• 查看存储过程的状态

SHOW PROCEDURE STATUS LIKE 存储过程名;

• 查看存储过程的定义

SHOW CREATE PROCEDURE 存储过程名;

• 删除存储过程

DROP PROCEDURE [ IF EXISTS ] <过程名>

事件（event）相关基本操作

• 查看事件信息

SELECT * FROM mysql.event;

• 开启/关闭定时器

SET GLOBAL event_scheduler = 1; -- 开启定时器 0：off 1：on 

• 查看是否开启定时器

SHOW VARIABLES LIKE 'event_scheduler';

• 修改配置，使默认开启事件（否则重启MySQL服务会被关闭）
  在my.ini配置文件的[mysqld]部分加上
  event_scheduler=ON