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

客官°小女子只卖身不卖艺 2023-09-25 16:20 75阅读 0赞

## 1.mysql定时任务简单介绍 ##

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

## 2.准备工作 ##

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

show variables like '%event_sche%';

执行结果如下

![85152b56f4f64e6ba691c36c0eba2bdd.png][]

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

--开启定时调度策略（下面两个语句都可以）
    set global event_scheduler=1;
    set global event_scheduler = on;
    
    --关闭定时调度策略（下面两个语句都可以）
    set global event_scheduler=0;
    set global event_scheduler = off;

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

![92a7195678c54c268552036938bcd7ab.png][]

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

![dbf8a51236b44308b22e59b3ae827962.png][]

### （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）中脚本执行完的结果：

![20e11e722e694323950f47d5ba9f6539.png][]

![5692c7568df14bcfa4fbe4088e1637db.png][]

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

![50f356ddf9384793b82dd50ffcb20bb4.png][]

以上存储过程主要分为三个阶段

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

脚本执行结果：

![f77ff22d5f6746349b0386ac02a779ff.png][]

查看定时任务：

select * from information_schema.EVENTS;

脚本执行结果:

![6ee96569a78845d190bde902789c777f.png][]

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

![4b2649d4a6a24f22a0ff14c12a24ee14.png][]

![818f77af1d3a4f1695f05b9b6693d502.png][]

停止定时任务执行：

ALTER event job_statistics on completion preserve disable;

继续定时任务：

ALTER event job_statistics on completion preserve enable;

[85152b56f4f64e6ba691c36c0eba2bdd.png]: https://img-blog.csdnimg.cn/img_convert/85152b56f4f64e6ba691c36c0eba2bdd.png
[92a7195678c54c268552036938bcd7ab.png]: https://img-blog.csdnimg.cn/img_convert/92a7195678c54c268552036938bcd7ab.png
[dbf8a51236b44308b22e59b3ae827962.png]: https://img-blog.csdnimg.cn/img_convert/dbf8a51236b44308b22e59b3ae827962.png
[20e11e722e694323950f47d5ba9f6539.png]: https://img-blog.csdnimg.cn/img_convert/20e11e722e694323950f47d5ba9f6539.png
[5692c7568df14bcfa4fbe4088e1637db.png]: https://img-blog.csdnimg.cn/img_convert/5692c7568df14bcfa4fbe4088e1637db.png
[50f356ddf9384793b82dd50ffcb20bb4.png]: https://img-blog.csdnimg.cn/img_convert/50f356ddf9384793b82dd50ffcb20bb4.png
[f77ff22d5f6746349b0386ac02a779ff.png]: https://img-blog.csdnimg.cn/img_convert/f77ff22d5f6746349b0386ac02a779ff.png
[6ee96569a78845d190bde902789c777f.png]: https://img-blog.csdnimg.cn/img_convert/6ee96569a78845d190bde902789c777f.png
[4b2649d4a6a24f22a0ff14c12a24ee14.png]: https://img-blog.csdnimg.cn/img_convert/4b2649d4a6a24f22a0ff14c12a24ee14.png
[818f77af1d3a4f1695f05b9b6693d502.png]: https://img-blog.csdnimg.cn/img_convert/818f77af1d3a4f1695f05b9b6693d502.png