mysql向表中批量插入数据

基础概念

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

优势

提高性能：批量插入减少了网络传输和数据库处理的开销。
减少锁竞争：批量插入可以减少对表的锁定时间，提高并发性能。
简化代码：相比于逐条插入，批量插入的代码更加简洁。

类型

使用INSERT INTO ... VALUES语句：
使用INSERT INTO ... VALUES语句：
使用INSERT INTO ... SELECT语句：
使用INSERT INTO ... SELECT语句：

应用场景

数据迁移：将数据从一个表或数据库迁移到另一个表或数据库。
批量导入：从文件或其他数据源批量导入数据到数据库。
初始化数据：在系统初始化时插入大量初始数据。

常见问题及解决方法

问题1：批量插入数据时速度慢

原因：

数据量过大，导致网络传输和数据库处理时间增加。
数据库服务器性能不足。
表结构设计不合理，索引过多或不合理。

解决方法：

分批次插入数据，减少单次插入的数据量。
优化数据库服务器配置，增加硬件资源。
优化表结构，减少不必要的索引。

问题2：批量插入数据时出现主键冲突

原因：

插入的数据中存在重复的主键值。

解决方法：

使用INSERT IGNORE或REPLACE INTO语句。
使用INSERT IGNORE或REPLACE INTO语句。
或
或

问题3：批量插入数据时出现事务超时

原因：

事务过大，导致数据库处理时间过长。

解决方法：

将大事务拆分为多个小事务。
调整数据库的事务超时时间。

示例代码

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

import mysql.connector

# 连接到MySQL数据库
cnx = mysql.connector.connect(user='username', password='password',
                              host='hostname', database='database_name')
cursor = cnx.cursor()

# 批量插入数据
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
data = [
    ('value1', 'value2', 'value3'),
    ('value4', 'value5', 'value6'),
    # ...
    ('valueN-2', 'valueN-1', 'valueN')
]

cursor.executemany(sql, data)
cnx.commit()

# 关闭连接
cursor.close()
cnx.close()