MySQL批量插入海量数据（存储过程实现与Python实现）

⚙️ 一、存储过程实现批量插入

通过MySQL内置的存储过程循环插入数据，适合数据库直接操作。

关键步骤：

1. 创建存储过程模板

DELIMITER $$
DROP PROCEDURE IF EXISTS batch_insert;
CREATE PROCEDURE batch_insert(IN loop_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET autocommit = 0;  -- 关闭自动提交，提升性能
    START TRANSACTION;
    WHILE i <= loop_count DO
        INSERT INTO users(user_name, create_date, remark)
        VALUES (CONCAT('user_', i), NOW(), CONCAT('remark_', i));
        SET i = i + 1;
    END WHILE;
    COMMIT;  -- 统一提交事务
END $$
DELIMITER ;

2. 调用存储过程

CALL batch_insert(1000000);  -- 插入100万条数据

⚡ 优化技巧：

• 分批插入：每插入2000条提交一次，避免大事务阻塞：

  IF MOD(i, 2000) = 0 THEN 
    COMMIT; 
    START TRANSACTION; 
  END IF;
  

• 动态生成随机数据：使用RAND()和MD5()生成随机字符串：

  SELECT SUBSTRING(MD5(RAND()), 1, 20) INTO @tmp_name;
  VALUES (@tmp_name, ...);
  

• 参数化控制：支持自定义起始ID和插入量，避免重复：

  CREATE PROCEDURE batch_insert(IN begin_id INT, IN loop_counts INT)
  ...
  VALUES (CONCAT('user_', begin_id + i), ...);
  

---

🐍 二、Python实现批量插入

适合通过应用程序动态生成数据并插入，使用executemany()。

基础实现：

import mysql.connector

db = mysql.connector.connect(
    host="localhost", 
    user="root", 
    password="123456", 
    database="test"
)
cursor = db.cursor()

# 生成测试数据（100万条）
data = [ (f"user_{i}", i % 100) for i in range(1000000) ]  # (name, age)

# 批量插入
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
cursor.executemany(sql, data) 
db.commit()  # 注意：需手动提交事务

⚡ 性能优化：

1. 分批次插入：

   batch_size = 50000  # 每批5万条
   for i in range(0, len(data), batch_size):
       cursor.executemany(sql, data[i:i+batch_size])
       db.commit()  # 分批提交，减少内存占用
   

2. 调整MySQL配置（my.cnf）：

   max_allowed_packet=256M  # 增大单次传输数据量
   innodb_buffer_pool_size=2G  # 提升InnoDB缓存
   

3. 禁用索引与约束：
   • 插入前临时禁用索引：ALTER TABLE users DISABLE KEYS;
   • 插入后重建：ALTER TABLE users ENABLE KEYS;

---

💎 三、方法对比与选型建议

方法	适用场景	性能	复杂度
存储过程	需在数据库内生成数据（如随机数据）	⭐⭐⭐⭐	中
Python单次提交	小批量数据（<10万条）	⭐⭐	低
Python分批提交	超大数据（>1000万条）	⭐⭐⭐⭐⭐	中

选型建议：

• 数据量 < 10万：直接用executemany()（代码简洁）。
• 数据量 > 1000万：
  • Python分批提交 + 禁用索引 + 调整MySQL配置。
  • 避免在存储过程中插入海量数据，可能触发max_sp_recursion_depth限制。
• 需动态生成复杂数据：存储过程更灵活（如生成随机字符串、时间序列等）。

---

💎 四、注意点

• 存储过程变量：声明VARCHAR必须指定长度（如DECLARE name VARCHAR(64)），否则报错。
• Python空值处理：None会被自动转为SQL的NULL。
• 事务一致性：无论哪种方法，务必显式控制事务（START TRANSACTION + COMMIT）以保证原子性。