使用MySQL存储过程分批批量插入百万级数据提速方案问询

没问题，针对你百万级数据分批插入的需求，我来给你一套适配source_names表结构的解决方案——用MySQL存储过程实现每批500条的批量插入，同时利用INSERT IGNORE规避唯一键冲突，最大化插入效率。

前置准备：数据源处理 #

首先，建议你先把所有待插入的source_name数据导入一个临时表（比如temp_source_names），这样批量读取的效率会更高。临时表的创建语句如下：

CREATE TEMPORARY TABLE temp_source_names (
    source_name VARCHAR(255) NOT NULL
);

你可以用LOAD DATA INFILE或者其他方式把百万条数据快速导入这个临时表，这比逐条构造插入语句高效得多。

核心存储过程代码 #

下面是实现分批插入的存储过程，每批插入500条数据：

DELIMITER //

CREATE PROCEDURE BatchInsertSourceNames()
BEGIN
    -- 定义每批插入的条数
    DECLARE batch_size INT DEFAULT 500;
    -- 待插入数据的总条数
    DECLARE total_rows INT;
    -- 当前偏移量，用于分批读取数据
    DECLARE current_offset INT DEFAULT 0;
    
    -- 获取临时表中待插入数据的总数量
    SELECT COUNT(*) INTO total_rows FROM temp_source_names;
    
    -- 循环执行分批插入
    WHILE current_offset < total_rows DO
        -- 批量插入500条数据，INSERT IGNORE会自动忽略重复的source_name
        INSERT IGNORE INTO source_names (source_name)
        SELECT source_name
        FROM temp_source_names
        LIMIT current_offset, batch_size;
        
        -- 更新偏移量，处理下一批数据
        SET current_offset = current_offset + batch_size;
        
        -- 可选：如果你的MySQL关闭了autocommit，这里可以手动提交事务
        -- COMMIT;
    END WHILE;
    
    -- 可选：插入完成后清理临时表
    -- DROP TEMPORARY TABLE IF EXISTS temp_source_names;
END //

DELIMITER ;

关键细节解释 #

• DELIMITER //：临时修改SQL分隔符，因为存储过程内部包含分号，避免MySQL提前解析存储过程的结束位置。
• batch_size：设置为你需要的每批插入条数（这里是500），你可以根据服务器性能调整（比如1000或2000，500是比较稳妥的默认值）。
• INSERT IGNORE：利用你表上的source_name_UNIQUE唯一索引，自动跳过重复的source_name，不会因为单条重复数据导致整批插入失败。
• 临时表数据源：用临时表作为数据源可以大幅提升读取效率，尤其是百万级数据量的场景。

扩展：如果需要动态生成示例数据 #

如果你是要生成类似a01、a02这样的示例数据，而不是从外部导入，可以用下面这个存储过程直接生成并分批插入：

DELIMITER //

CREATE PROCEDURE GenerateAndBatchInsertSourceNames(IN total_records INT)
BEGIN
    DECLARE batch_size INT DEFAULT 500;
    DECLARE current_count INT DEFAULT 0;
    DECLARE start_num INT DEFAULT 1;
    
    WHILE current_count < total_records DO
        -- 构造批量插入的SQL语句
        SET @sql = 'INSERT IGNORE INTO source_names (source_name) VALUES ';
        SET @values = '';
        SET @i = 0;
        
        -- 循环构造当前批次的500条数据值
        WHILE @i < batch_size AND (current_count + @i) < total_records DO
            SET @value = CONCAT('(\'a', LPAD(start_num + @i, 2, '0'), '\')');
            IF @i > 0 THEN
                SET @values = CONCAT(@values, ',', @value);
            ELSE
                SET @values = @value;
            END IF;
            SET @i = @i + 1;
        END WHILE;
        
        -- 执行动态构造的SQL
        SET @sql = CONCAT(@sql, @values);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 更新计数和起始编号
        SET current_count = current_count + @i;
        SET start_num = start_num + @i;
    END WHILE;
END //

DELIMITER ;

调用方式很简单，比如要插入100万条数据：

CALL GenerateAndBatchInsertSourceNames(1000000);

内容的提问来源于stack exchange，提问作者morteza ali ahmadi