系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

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

时间:2023-03-19来源:系统城装机大师作者:佚名

1.mysql定时任务简单介绍

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

2.准备工作

(1)查看定时策略是否开启

1 show variables like '%event_sche%';

执行结果如下

ON表示处于开启状态,如果是OFF则表示处于关闭状态,假设处于关闭状态,使用下面sql语句开启和关闭就行。

1
2
3
4
5
6
7
--开启定时调度策略(下面两个语句都可以)
set global event_scheduler=1;
set global event_scheduler = on;
  
--关闭定时调度策略(下面两个语句都可以)
set global event_scheduler=0;
set global event_scheduler = off;

关闭定时调度策略sql执行结果:

开启定时调度策略sql执行结果:

(2)执行用户信息表和用户订单表sql脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 用户信息表
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脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 向用户信息表中插入三条测试数据
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.编写存储过程脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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钟统计一次用户的订单数量。

1
2
3
4
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;

到此这篇关于利用Mysql定时+存储过程创建临时表统计数据的文章就介绍到这了

分享到:

相关信息

  • Mysql指定某个字符串字段前面几位排序查询方式

    指定某个字符串字段前面几位排序查询 数据样例 第一步(想办法先截取到 ORDER关键字前面的 值) 第二步,直接根据NO排序即可? (有坑) 第三步转换排序...

    2023-03-17

  • Mysql存储二进制对象数据问题

    Mysql存储二进制对象数据 首先数据库存储一个Object对象 与数据库对应的实体类 编写一个操作二进制的工具类 Mysql存储二进制大型对象类型对照 MySql MediumBlob——MySql的Bolb四种类型...

    2023-03-15

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载