MySQL变量赋值与基于design_sheet动态插入数据至汇总表咨询

需求场景说明 #

我要实现的是这样一个数据汇总需求：

• 数据库里有个配置表 design_sheet，每一行都存着 COUNTYID、YEAR、METHOD 这三个字段的值
• 同时还有一堆按固定规则命名的源数据表，命名格式是：sb16im2yr_c{COUNTYID}y{YEAR}_{METHOD}_9653_20MY_out，这里的占位符对应design_sheet里的字段值
• 目标是通过design_sheet里的配置动态匹配对应的源表，再用MySQL变量赋值的方式，把这些源表的数据批量插入到指定的汇总表里

核心实现代码 #

核心逻辑是借助动态SQL拼接完成的，完整实现代码如下：

-- 先定义汇总库和汇总表的变量，替换成你实际的库表名
SET @SUMMARY = 'your_summary_db';
SET @SUMMARY_TB = 'your_summary_table';

-- 创建存储过程批量处理所有配置项
DELIMITER //
CREATE PROCEDURE batch_sync_source_to_summary()
BEGIN
    DECLARE is_done INT DEFAULT FALSE;
    DECLARE c_id VARCHAR(64);
    DECLARE y_val INT;
    DECLARE m_val VARCHAR(64);
    DECLARE source_table VARCHAR(256);
    DECLARE sync_sql TEXT;
    
    -- 声明游标读取design_sheet里的所有配置
    DECLARE config_cursor CURSOR FOR SELECT COUNTYID, YEAR, METHOD FROM design_sheet;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = TRUE;
    
    OPEN config_cursor;
    config_loop: LOOP
        FETCH config_cursor INTO c_id, y_val, m_val;
        IF is_done THEN
            LEAVE config_loop;
        END IF;
        
        -- 按照命名规则拼接对应的源表名
        SET source_table = CONCAT('sb16im2yr_c', c_id, 'y', y_val, '_', m_val, '_9653_20MY_out');
        
        -- 动态生成插入语句（假设汇总表和源表结构一致，用SELECT *插入）
        SET sync_sql = CONCAT(
            'INSERT INTO ', @SUMMARY, '.`', @SUMMARY_TB, '` ',
            'SELECT * FROM `', source_table, '`'
        );
        
        -- 执行动态SQL
        PREPARE sync_stmt FROM sync_sql;
        EXECUTE sync_stmt;
        DEALLOCATE PREPARE sync_stmt;
    END LOOP;
    CLOSE config_cursor;
END //
DELIMITER ;

-- 调用存储过程开始同步数据
CALL batch_sync_source_to_summary();

关键注意点 #

• 务必保证design_sheet里的COUNTYID、YEAR、METHOD值和源表命名完全匹配，否则会触发「表不存在」的错误
• 汇总表的结构必须和所有源表的结构保持一致（字段数量、类型、顺序都要对应），否则插入会失败
• 执行存储过程的数据库账号，需要拥有所有源表的查询权限以及汇总表的插入权限
• 如果源表数据量较大，建议在存储过程中添加事务控制，或者分批次处理，避免长时间锁表影响其他业务

内容的提问来源于stack exchange，提问作者Jiaoyan Huang