mysql表数据批量添加

基础概念

MySQL表数据批量添加是指一次性向数据库表中插入多条记录的操作。相比于逐条插入数据，批量添加可以显著提高数据插入的效率。

优势

1. 效率高：减少了与数据库的交互次数，降低了网络开销和数据库负载。
2. 性能提升：批量插入可以利用数据库的内部优化机制，如事务处理，进一步提高性能。
3. 代码简洁：减少了重复代码，使代码更加简洁易读。

类型

1. 使用INSERT INTO ... VALUES语句：
2. 使用INSERT INTO ... VALUES语句：
3. 使用LOAD DATA INFILE语句： 适用于从文件中批量导入数据。
4. 使用LOAD DATA INFILE语句： 适用于从文件中批量导入数据。

应用场景

1. 数据迁移：将数据从一个数据库迁移到另一个数据库时，批量插入可以大大提高迁移速度。
2. 数据初始化：在系统初始化或测试时，需要插入大量测试数据。
3. 日志记录：在高并发场景下，批量插入日志数据可以减少对数据库的压力。

常见问题及解决方法

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

原因：插入的数据中存在重复的主键或唯一索引值。

解决方法：

• 检查并确保插入的数据中没有重复的主键或唯一索引值。
• 使用INSERT IGNORE语句忽略重复的记录。
• 使用INSERT IGNORE语句忽略重复的记录。
• 使用ON DUPLICATE KEY UPDATE语句更新重复的记录。
• 使用ON DUPLICATE KEY UPDATE语句更新重复的记录。

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

原因：可能是由于网络延迟、数据库负载过高或SQL语句编写不当等原因。

解决方法：

• 使用事务处理，将批量插入操作放在一个事务中。
• 使用事务处理，将批量插入操作放在一个事务中。
• 调整MySQL配置参数，如innodb_buffer_pool_size、innodb_log_file_size等，以提高性能。
• 确保插入的数据已经进行了适当的预处理，如去重、排序等。

示例代码

以下是一个使用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, "记录插入成功。")

参考链接

• MySQL官方文档 - INSERT语句
• MySQL官方文档 - LOAD DATA INFILE语句
• MySQL Connector/Python官方文档