需求分析

需要定时查询现有的一张表，然后将查询结果插入到另一张表。

查询的表结构（表名：mouse_tbl）如下图

mysql怎么根据A表查询结果更新B表的字段 mysql查询结果作为新表_MySQL


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

mysql怎么根据A表查询结果更新B表的字段 mysql查询结果作为新表_MySQL_02


我们的需求是将表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