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

创建临时表统计数据是Mysql中一种常见且高效的统计方式。这种方式通过利用Mysql的定时任务和存储过程，将统计数据存储在临时表中，从而降低主查询的复杂度，提高查询效率。

下面是创建临时表统计数据的完整攻略。

步骤一：创建存储过程

创建存储过程是创建临时表统计数据的关键步骤。在存储过程中，需要使用以下语句：

1. 删除已有的临时表

DROP TEMPORARY TABLE IF EXISTS `temp_table`;

1. 创建新的临时表

CREATE TEMPORARY TABLE `temp_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `count` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
);

1. 统计数据并插入临时表

INSERT INTO temp_table (name, count)
SELECT name, COUNT(*) AS count
FROM your_table
GROUP BY name;

1. 设置存储过程结果

SELECT * FROM temp_table;

完整示例代码：

CREATE PROCEDURE `proc_temp_table`()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `temp_table`;

  CREATE TEMPORARY TABLE `temp_table` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
  );

  INSERT INTO temp_table (name, count)
  SELECT name, COUNT(*) AS count
  FROM your_table
  GROUP BY name;

  SELECT * FROM temp_table;
END;

步骤二：定时执行存储过程

为了定时执行存储过程，我们需要使用Mysql的事件调度器。通过以下步骤设置调度器：

1. 启动事件调度器

SET GLOBAL event_scheduler = ON;

1. 创建事件调度器

CREATE EVENT `event_temp_table`
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-19 00:00:00'
DO CALL `proc_temp_table`();

1. 查看事件调度器列表

SHOW EVENTS;

1. 删除事件调度器

DROP EVENT IF EXISTS `event_temp_table`;

完整示例代码：

在每天的23:59:59分执行一次统计数据的存储过程。

SET GLOBAL event_scheduler = ON;

CREATE EVENT `event_temp_table`
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-19 00:00:00'
DO CALL `proc_temp_table`();

SHOW EVENTS;

DROP EVENT IF EXISTS `event_temp_table`;

示例说明

以用户注册量统计为例，举两个示例说明：

示例一：统计所有用户的注册数量

假设我们有一个用户表user，包含以下字段：

• id：用户id
• name：用户名
• created_at：创建时间

我们可以创建一个存储过程proc_temp_user来统计每个用户名下的注册数量：

CREATE PROCEDURE `proc_temp_user`()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `temp_user`;

  CREATE TEMPORARY TABLE `temp_user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
  );

  INSERT INTO temp_user (name, count)
  SELECT name, COUNT(*) AS count
  FROM user
  GROUP BY name;

  SELECT * FROM temp_user;
END;

然后我们可以使用事件调度器在每天的23:59:59分执行一次存储过程：

SET GLOBAL event_scheduler = ON;

CREATE EVENT `event_temp_user`
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-19 00:00:00'
DO CALL `proc_temp_user`();

SHOW EVENTS;

DROP EVENT IF EXISTS `event_temp_user`;

示例二：统计最近30天内注册的用户数量

同样地，我们可以通过修改存储过程中的查询条件来统计最近30天内注册的用户数量：

CREATE PROCEDURE `proc_temp_user_recent`()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `temp_user_recent`;

  CREATE TEMPORARY TABLE `temp_user_recent` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `count` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
  );

  INSERT INTO temp_user_recent (name, count)
  SELECT name, COUNT(*) AS count
  FROM user
  WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY name;

  SELECT * FROM temp_user_recent;
END;

然后我们同样可以使用事件调度器在每天的23:59:59分执行一次存储过程：

SET GLOBAL event_scheduler = ON;

CREATE EVENT `event_temp_user_recent`
ON SCHEDULE EVERY 1 DAY STARTS '2021-10-19 00:00:00'
DO CALL `proc_temp_user_recent`();

SHOW EVENTS;

DROP EVENT IF EXISTS `event_temp_user_recent`;

这样一来，我们就可以通过定时执行存储过程并将结果存储在临时表中的方式，高效地统计数据，提高查询效率。