怎么用Mysql定时+存储过程创建临时表统计数据
这篇“怎么用Mysql定时+存储过程创建临时表统计数据”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“怎么用Mysql定时+存储过程创建临时表统计数据”文章吧。
1.mysql定时任务简单介绍
mysql的定时任务是使用event（事件）来实现的，自mysql5.1.6版本起，增加了这个功能 - 事件调度器（event scheduler），它可以精确到每秒钟执行一个任务，在一些对数据实时性要求比较高的场景非常使用，接下来我将用mysql的event事件来实现定时统计数据。
2.准备工作
（1）查看定时策略是否开启
show variables like '%event_sche%';
执行结果如下
ON表示处于开启状态，如果是OFF则表示处于关闭状态，假设处于关闭状态，使用下面sql语句开启和关闭就行。
--开启定时调度策略（下面两个语句都可以） set global event_scheduler=1; set global event_scheduler = on; --关闭定时调度策略（下面两个语句都可以） set global event_scheduler=0; set global event_scheduler = off;
关闭定时调度策略sql执行结果：
开启定时调度策略sql执行结果：
（2）执行用户信息表和用户订单表sql脚本
# 用户信息表 CREATE TABLE `user_info` ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名', `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手机号', `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用户状态:停用0，启动1', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY ( `id` ) USING BTREE ) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表'; # 用户订单表 CREATE TABLE `user_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `order_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单编号', `user_id` int(11) NOT NULL COMMENT '用户ID', `create_time` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `idx_order_num`(`order_num`) USING BTREE COMMENT '订单编号唯一' ) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户订单表';
（3）执行插入测试数据sql脚本
# 向用户信息表中插入三条测试数据 INSERT INTO `user_info` (`id`, `name`, `phone`, `status`, `create_time`) VALUES (10001, '张三', '13900669010', 1, '2023-03-14 17:01:42'); INSERT INTO `user_info` (`id`, `name`, `phone`, `status`, `create_time`) VALUES (10002, '李四', '13900669111', 1, '2023-03-14 17:01:42'); INSERT INTO `user_info` (`id`, `name`, `phone`, `status`, `create_time`) VALUES (10003, '王五', '13900669876', 1, '2023-03-14 17:01:42'); # 向用户订单表中插入八条测试数据 INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10001, 'dingdan001', 10002, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10002, 'dingdan002', 10003, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10003, 'dingdan003', 10002, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10004, 'dingdan004', 10002, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10005, 'dingdan005', 10003, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10006, 'dingdan006', 10003, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10007, 'dingdan007', 10002, '2023-03-14 17:03:40'); INSERT INTO `user_order` (`id`, `order_num`, `user_id`, `create_time`) VALUES (10008, 'dingdan008', 10001, '2023-03-14 17:03:40');
（2）（3）中脚本执行完的结果：
3.编写存储过程脚本
DELIMITER // DROP PROCEDURE IF EXISTS statistics_user_order // CREATE PROCEDURE statistics_user_order () BEGIN DECLARE temp_table_name VARCHAR ( 60 ) DEFAULT ''; DECLARE suffix VARCHAR ( 10 ) DEFAULT ''; DECLARE old_table_name VARCHAR ( 60 ) DEFAULT NULL; SELECT table_name INTO old_table_name FROM information_schema.`TABLES` WHERE table_name LIKE 'temp_statistics_%' AND table_schema = 'db_name'; -- 此处填自己对应的数据库名即可 IF old_table_name IS NOT NULL THEN -- execute multiple statements -- 如果IF THEN ... END IF块内有多个语句,最好将它们放在一个BEGIN ... END;块中 BEGIN SET @drop_sql := CONCAT( 'DROP TABLE ', old_table_name, ';' ); PREPARE d_sql FROM @drop_sql; EXECUTE d_sql; DEALLOCATE PREPARE d_sql; END; END IF; SELECT DATE_FORMAT( NOW(), '%Y%m%d' ) INTO suffix; SET temp_table_name = CONCAT( 'temp_statistics_', suffix ); SET @create_sql = CONCAT( 'create table if not exists ', temp_table_name, "( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `user_id` INT ( 11 ) NOT NULL COMMENT '用户ID', `name` VARCHAR ( 30 ) NOT NULL COMMENT '用户名', `number` INT ( 11 ) NOT NULL DEFAULT 0 COMMENT '订单数', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY ( `id` ) USING BTREE ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户订单统计表';" ); PREPARE pre_stmt FROM @create_sql; EXECUTE pre_stmt; DEALLOCATE PREPARE pre_stmt; -- 简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。 SET @insert_sql = CONCAT( 'INSERT INTO ', temp_table_name, "( `user_id`, `name`, `number` ) SELECT i.id AS `user_id`, i.`name` AS `name`, COUNT( o.user_id ) AS `number` FROM user_info i LEFT JOIN user_order o ON i.id = o.user_id WHERE i.`status` = 1 GROUP BY i.id;" ); PREPARE pre_insert FROM @insert_sql; EXECUTE pre_insert; DEALLOCATE PREPARE pre_insert; END // DELIMITER;
脚本执行结果：(注意：上述存储过程中的数据库不要忘记更改"AND table_schema = 'db_name'; -- 此处填自己对应的数据库名即可")
以上存储过程主要分为三个阶段
a.检查数据库中临时表是否存在，如果存在则删除表结构（移除老表）
b.根据当前时间创建新的临时表，表结构根据统计需要增加字段
c.联表查询，将每个用户所拥有的订单数量统计，并插入到临时表中去
4.编写和执行定时任务脚本
为了让大家看到更显著的效果，将定时任务设置为每10秒钟执行一次，也就是这个定时任务的功能是10s钟统计一次用户的订单数量。
create event job_statistics -- 是创建名为job_statistics的事件； on schedule every 10 SECOND -- 创建周期定时的规则，意思是每10s种执行一次； on completion preserve enable -- 是表示创建后就开始生效，不让开始生效设置disable do call statistics_user_order(); -- 事件要执行的内容，调用了上述的存储过程
脚本执行结果：
查看定时任务：
select * from information_schema.EVENTS;
脚本执行结果:
查看定时任务执行效果：（看下面的时间差，定时在刷新）
停止定时任务执行：
ALTER event job_statistics on completion preserve disable;
继续定时任务：
ALTER event job_statistics on completion preserve enable;
以上就是关于“怎么用Mysql定时+存储过程创建临时表统计数据”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注蜗牛博客行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：niceseo99@gmail.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
版权声明：如无特殊标注，文章均为本站原创，转载时请以链接形式注明文章出处。
评论