如何遍历所有Schema统计同名表记录数并通过Crontab每5分钟自动化执行

一、编写跨Schema统计函数 #

根据你使用的数据库类型，选择对应的实现：

PostgreSQL 实现 #

创建PL/pgSQL函数，遍历指定Schema并统计table1的记录数：

CREATE OR REPLACE FUNCTION count_table1_across_schemas()
RETURNS TABLE(schema_name TEXT, record_count BIGINT) AS $$
DECLARE
    schema_rec RECORD;
BEGIN
    -- 替换为你的5个实际Schema名称
    FOR schema_rec IN SELECT unnest(ARRAY['schema1', 'schema2', 'schema3', 'schema4', 'schema5']) AS schema_name LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L AS schema_name, COUNT(*) AS record_count FROM %I.table1',
            schema_rec.schema_name,
            schema_rec.schema_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

MySQL 实现 #

创建存储过程完成统计：

DELIMITER //
CREATE PROCEDURE count_table1_across_schemas()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE schema_name VARCHAR(255);
    -- 指定要遍历的Schema列表
    DECLARE cur CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name IN ('schema1','schema2','schema3','schema4','schema5');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO schema_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('SELECT "', schema_name, '" AS schema_name, COUNT(*) AS record_count FROM ', schema_name, '.table1');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

二、编写可执行脚本（供Crontab调用） #

创建Shell脚本count_table1.sh，用于命令行执行统计函数并输出日志：

#!/bin/bash
# 替换为你的数据库连接信息
DB_USER="your_username"
DB_NAME="your_database"
LOG_PATH="/var/log/table1_count.log"

# PostgreSQL 版本执行命令
psql -U $DB_USER -d $DB_NAME -c "SELECT * FROM count_table1_across_schemas();" >> $LOG_PATH 2>&1

# 若使用MySQL，替换为以下命令（建议通过~/.my.cnf配置免密登录，避免明文密码）
# mysql -u $DB_USER $DB_NAME -e "CALL count_table1_across_schemas();" >> $LOG_PATH 2>&1

给脚本添加执行权限：

chmod +x /path/to/count_table1.sh

三、配置Crontab实现每5分钟定时执行 #

编辑当前用户的Crontab任务：

crontab -e

添加以下定时任务（替换为脚本的绝对路径）：

*/5 * * * * /path/to/count_table1.sh

保存退出后，Crontab会自动加载任务，每5分钟执行一次统计脚本。

注意事项 #

• 确保数据库用户拥有访问所有目标Schema及table1的权限
• 日志路径需具备写入权限，可根据需求调整输出格式或存储位置
• MySQL用户建议通过~/.my.cnf配置免密登录，避免脚本中明文存储密码
• 若Schema列表动态变化，可修改函数/存储过程中的Schema筛选逻辑（比如从系统表中读取符合条件的Schema）

内容的提问来源于stack exchange，提问作者sandesh Jadhav