批量写入数据到mysql

基础概念

批量写入数据到MySQL是指一次性将多条记录插入到数据库中，而不是逐条插入。这种操作可以显著提高数据插入的效率，减少与数据库的交互次数。

优势

提高性能：批量插入减少了网络开销和数据库的I/O操作，从而提高了数据插入的速度。
减少锁竞争：批量插入可以减少对数据库表的锁竞争，特别是在高并发环境下。
简化代码：批量插入可以简化代码逻辑，减少循环插入的复杂性。

类型

使用INSERT INTO ... VALUES (...), (...), ...语法：
使用INSERT INTO ... VALUES (...), (...), ...语法：
使用LOAD DATA INFILE语法：
使用LOAD DATA INFILE语法：

应用场景

数据导入：从外部文件或其他数据库导入大量数据时。
日志记录：批量记录系统日志或用户操作日志。
批量更新：需要批量更新大量记录时。

常见问题及解决方法

问题1：批量插入数据时遇到`MySQL server has gone away`错误

原因：这个错误通常是由于MySQL服务器的wait_timeout或interactive_timeout设置过短，导致连接超时。

解决方法：

增加wait_timeout和interactive_timeout的值：
增加wait_timeout和interactive_timeout的值：
在应用程序中使用连接池，保持连接的活跃状态。

问题2：批量插入数据时遇到`Duplicate entry`错误

原因：插入的数据中存在重复的键值。

解决方法：

使用INSERT IGNORE语句忽略重复键值错误：
使用INSERT IGNORE语句忽略重复键值错误：
使用ON DUPLICATE KEY UPDATE语句更新重复键值：
使用ON DUPLICATE KEY UPDATE语句更新重复键值：

问题3：批量插入数据时性能不佳

原因：可能是由于网络延迟、数据库服务器性能瓶颈或SQL语句优化不当。

解决方法：

使用批量插入语句，减少与数据库的交互次数。
优化数据库服务器配置，增加内存和CPU资源。
使用索引优化查询性能，但要注意索引过多会影响插入性能。
考虑使用分布式数据库或分片技术来分担负载。

示例代码

以下是一个使用Python和MySQL Connector库进行批量插入的示例：

import mysql.connector

# 连接到MySQL数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

mycursor = mydb.cursor()

# 批量插入数据
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('John', 'Highway 21'),
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) inserted.")