利用Mysql定時(shí)+存儲過程創(chuàng)建臨時(shí)表統(tǒng)計(jì)數(shù)據(jù)的過程

更新時(shí)間：2023年03月18日 11:32:10 作者：自律的西瓜L

這篇文章主要介紹了利用Mysql定時(shí)+存儲過程創(chuàng)建臨時(shí)表統(tǒng)計(jì)數(shù)據(jù),本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

1.mysql定時(shí)任務(wù)簡單介紹

mysql的定時(shí)任務(wù)是使用event（事件）來實(shí)現(xiàn)的，自mysql5.1.6版本起，增加了這個(gè)功能 - 事件調(diào)度器（event scheduler），它可以精確到每秒鐘執(zhí)行一個(gè)任務(wù)，在一些對數(shù)據(jù)實(shí)時(shí)性要求比較高的場景非常使用，接下來我將用mysql的event事件來實(shí)現(xiàn)定時(shí)統(tǒng)計(jì)數(shù)據(jù)。

2.準(zhǔn)備工作

（1）查看定時(shí)策略是否開啟

show variables like '%event_sche%';

執(zhí)行結(jié)果如下

ON表示處于開啟狀態(tài)，如果是OFF則表示處于關(guān)閉狀態(tài)，假設(shè)處于關(guān)閉狀態(tài)，使用下面sql語句開啟和關(guān)閉就行。

--開啟定時(shí)調(diào)度策略（下面兩個(gè)語句都可以）
set global event_scheduler=1;
set global event_scheduler = on;
 
--關(guān)閉定時(shí)調(diào)度策略（下面兩個(gè)語句都可以）
set global event_scheduler=0;
set global event_scheduler = off;

關(guān)閉定時(shí)調(diào)度策略sql執(zhí)行結(jié)果：

開啟定時(shí)調(diào)度策略sql執(zhí)行結(jié)果：

（2）執(zhí)行用戶信息表和用戶訂單表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 '手機(jī)號',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用戶狀態(tài):停用0，啟動(dòng)1',
    `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
    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 '創(chuàng)建時(shí)間',
  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）執(zhí)行插入測試數(shù)據(jù)sql腳本

# 向用戶信息表中插入三條測試數(shù)據(jù)
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');
 
# 向用戶訂單表中插入八條測試數(shù)據(jù)
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）中腳本執(zhí)行完的結(jié)果：

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'; -- 此處填自己對應(yīng)的數(shù)據(jù)庫名即可
    IF
        old_table_name IS NOT NULL THEN
        -- execute multiple statements
        -- 如果IF THEN ... END IF塊內(nèi)有多個(gè)語句,最好將它們放在一個(gè)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 '訂單數(shù)',
        `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
        PRIMARY KEY ( `id` ) USING BTREE 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶訂單統(tǒng)計(jì)表';" );
    PREPARE pre_stmt 
    FROM
        @create_sql;
    EXECUTE pre_stmt;
    DEALLOCATE PREPARE pre_stmt;
    -- 簡單的用set或者declare語句定義變量，然后直接作為sql的表名是不行的，mysql會(huì)把變量名當(dāng)作表名。
    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;

腳本執(zhí)行結(jié)果：(注意：上述存儲過程中的數(shù)據(jù)庫不要忘記更改"AND table_schema = 'db_name'; -- 此處填自己對應(yīng)的數(shù)據(jù)庫名即可")

以上存儲過程主要分為三個(gè)階段

a.檢查數(shù)據(jù)庫中臨時(shí)表是否存在，如果存在則刪除表結(jié)構(gòu)（移除老表）

b.根據(jù)當(dāng)前時(shí)間創(chuàng)建新的臨時(shí)表，表結(jié)構(gòu)根據(jù)統(tǒng)計(jì)需要增加字段

c.聯(lián)表查詢，將每個(gè)用戶所擁有的訂單數(shù)量統(tǒng)計(jì)，并插入到臨時(shí)表中去

4.編寫和執(zhí)行定時(shí)任務(wù)腳本

為了讓大家看到更顯著的效果，將定時(shí)任務(wù)設(shè)置為每10秒鐘執(zhí)行一次，也就是這個(gè)定時(shí)任務(wù)的功能是10s鐘統(tǒng)計(jì)一次用戶的訂單數(shù)量。

create event job_statistics -- 是創(chuàng)建名為job_statistics的事件；
on schedule every 10 SECOND -- 創(chuàng)建周期定時(shí)的規(guī)則，意思是每10s種執(zhí)行一次；
on completion preserve enable --  是表示創(chuàng)建后就開始生效，不讓開始生效設(shè)置disable
do call statistics_user_order(); -- 事件要執(zhí)行的內(nèi)容，調(diào)用了上述的存儲過程

腳本執(zhí)行結(jié)果：

查看定時(shí)任務(wù)：

select * from information_schema.EVENTS;

腳本執(zhí)行結(jié)果:

查看定時(shí)任務(wù)執(zhí)行效果：（看下面的時(shí)間差，定時(shí)在刷新）

停止定時(shí)任務(wù)執(zhí)行：

ALTER event job_statistics on completion preserve disable;

繼續(xù)定時(shí)任務(wù)：

ALTER event job_statistics on completion preserve enable;

到此這篇關(guān)于利用Mysql定時(shí)+存儲過程創(chuàng)建臨時(shí)表統(tǒng)計(jì)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)mysql創(chuàng)建臨時(shí)表統(tǒng)計(jì)數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: