SQL数据库多表行数每周统计存入带时间戳日志表，记录刷新增量

1. 创建统计日志表 #

首先创建用于存储统计结果的日志表，核心字段覆盖统计时间、目标表名、当前行数及增量：

CREATE TABLE table_row_count_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键，唯一标识每条记录
    stat_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, -- 统计执行时间戳
    table_name VARCHAR(100) NOT NULL, -- 被统计的数据表名
    current_row_count BIGINT NOT NULL, -- 统计时的表行数
    row_increment BIGINT NOT NULL -- 相较于上一次统计的行数增量
);

2. 编写统计及插入日志的脚本/存储过程 #

以下以MySQL为例，提供两种实现方式，其他数据库（如SQL Server、PostgreSQL）可参照逻辑调整语法：

方式一：动态遍历表的存储过程 #

适合需要统计大量表的场景，可通过游标批量处理：

DELIMITER //
CREATE PROCEDURE collect_table_row_counts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(100);
    DECLARE current_count BIGINT;
    DECLARE last_count BIGINT;
    DECLARE increment BIGINT;

    -- 游标读取目标表列表（替换为你的数据库名和需要统计的表）
    DECLARE table_cursor CURSOR FOR
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'your_db_name'
          AND table_name IN ('user_info', 'order_list', 'product_data');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN table_cursor;

    read_loop: LOOP
        FETCH table_cursor INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 统计当前表行数
        SET @sql = CONCAT('SELECT COUNT(*) INTO @current_count FROM ', tbl_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET current_count = @current_count;

        -- 获取该表上一次统计的行数（首次统计则取0）
        SELECT COALESCE(current_row_count, 0) INTO last_count
        FROM table_row_count_log
        WHERE table_name = tbl_name
        ORDER BY stat_timestamp DESC
        LIMIT 1;

        -- 计算增量
        SET increment = current_count - last_count;

        -- 插入日志
        INSERT INTO table_row_count_log (table_name, current_row_count, row_increment)
        VALUES (tbl_name, current_count, increment);
    END LOOP;

    CLOSE table_cursor;
END //
DELIMITER ;

方式二：静态批量统计脚本 #

适合少量固定表的场景，代码更简洁：

INSERT INTO table_row_count_log (table_name, current_row_count, row_increment)
SELECT 
    'user_info' AS table_name,
    (SELECT COUNT(*) FROM user_info) AS current_count,
    (SELECT COUNT(*) FROM user_info) - COALESCE((SELECT current_row_count FROM table_row_count_log WHERE table_name='user_info' ORDER BY stat_timestamp DESC LIMIT 1), 0) AS row_increment
UNION ALL
SELECT 
    'order_list' AS table_name,
    (SELECT COUNT(*) FROM order_list) AS current_count,
    (SELECT COUNT(*) FROM order_list) - COALESCE((SELECT current_row_count FROM table_row_count_log WHERE table_name='order_list' ORDER BY stat_timestamp DESC LIMIT 1), 0) AS row_increment;

3. 设置每周定时执行任务 #

根据数据库类型配置定时任务，确保每周自动执行统计：

MySQL #

创建每周日凌晨2点执行的事件（需先开启事件调度器：SET GLOBAL event_scheduler = ON;）：

CREATE EVENT weekly_row_count_stat
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-01 02:00:00'
DO
    CALL collect_table_row_counts();

SQL Server #

通过SQL Server代理创建作业：

1. 新建作业，添加执行步骤：EXEC collect_table_row_counts;
2. 配置调度为每周指定时间（如周日凌晨2点）

PostgreSQL #

使用pg_cron扩展（需提前安装）创建定时任务：

SELECT cron.schedule('weekly-row-count-stat', '0 2 * * 0', 'CALL collect_table_row_counts();');

0 2 * * 0代表每周日凌晨2点执行。

内容的提问来源于stack exchange，提问作者Ajitosh Anand