利用Mysql定时+存储过程创建临时表统计数据的过程_F11

分享到

利用Mysql定时+存储过程创建临时表统计数据的过程

Mysql 来源：互联网作者：佚名发布时间：2024-02-19 22:32:37 人浏览

摘要

1.mysql定时任务简单介绍 mysql的定时任务是使用event（事件）来实现的，自mysql5.1.6版本起，增加了这个功能 - 事件调度器（event scheduler），它可以精确到每秒钟执行一个任务，在一些对数据实时

1.mysql定时任务简单介绍

mysql的定时任务是使用event（事件）来实现的，自mysql5.1.6版本起，增加了这个功能 - 事件调度器（event scheduler），它可以精确到每秒钟执行一个任务，在一些对数据实时性要求比较高的场景非常使用，接下来我将用mysql的event事件来实现定时统计数据。

2.准备工作

（1）查看定时策略是否开启

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

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'; -- 此处填自己对应的数据库名即可

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(); -- 事件要执行的内容，调用了上述的存储过程

脚本执行结果：

查看定时任务：

1	select * from information_schema.EVENTS;

脚本执行结果:

查看定时任务执行效果：（看下面的时间差，定时在刷新）

停止定时任务执行：

1	ALTER event job_statistics on completion preserve disable;

继续定时任务：

1	ALTER event job_statistics on completion preserve enable;

您可能感兴趣的文章 :

原文链接 :

Tag : mysql(237)

mysql-5.7.42升级到mysql-8.2.0(二进制方式)

8注：本文在测试环境升级测试，建议先在测试环境验证。在生产环境下还是先评估下，mysql-5.7.42为二进制方式安装，所以用mysql-8.2.0二进制
利用Mysql定时+存储过程创建临时表统计数据的过

1.mysql定时任务简单介绍 mysql的定时任务是使用event（事件）来实现的，自mysql5.1.6版本起，增加了这个功能 - 事件调度器（event scheduler），它
MySQL如何查询Binlog生成时间

本篇文章介绍如何查询 Binlog 的生成时间。云上 RDS 有日志管理，但是自建实例没有，该脚本可用于自建实例闪回定位 Binlog 文件。脚本介绍
order by +limit分页时数据重复问题及解决方法

问题描述：MYSQL version 5.6.8command 表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE command ( ID INT NOT NULL, NAME VARCHAR(16), DESCRIPTION VARCHAR(32), INDE
MySQL本地版本升级超详细教程(从5.5.20升到8.0.21)

写在前面升级本地mysql，当前5.5.20，升级前先备份本地的数据库文件以防数据丢失一、卸载本地的旧版本 1. 停止MySQL服务 win+R 打开运行，
MySQL数据库的多种连接方式及工具介绍

一、MySQl命令行客户端在Mysql安装完成后，Mysql命令行会自动配置在计算机上，在菜单中找到MySql 8.0 Command Line Client,输入密码即可进入。（密
MySQL函数全面总结详解示例分析教程

1.MySQL中关于函数的说明概念：类似java、python中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名；好处：Ⅰ隐藏了实现细节；Ⅱ
MySQL一键安装Shell脚本的实现

一、脚本说明 1、linux系统版本 EL6, EL7, EL8, and EL9-based platforms (for example, the corresponding versions of Oracle Linux, Red Hat Enterprise Linux, and CentOS)，本脚
mysql乐观锁和悲观锁的具体使用介绍

悲观锁介绍（百科）：悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保
mysql中TINYINT取值范围的介绍

项目场景：最近在开发过程中，看见表设计中是thinyint字段，但对于它的范围产生了好奇问题描述：当我们填写超过128数值的时候，该字段