mysql批量上传数据库

基础概念

MySQL 批量上传数据库是指将多条数据记录一次性插入到 MySQL 数据库中的操作。这种操作通常用于提高数据导入的效率，特别是在处理大量数据时。

优势

效率高：相比于逐条插入数据，批量插入可以显著减少与数据库的交互次数，从而提高数据导入的速度。
减少资源消耗：批量插入减少了数据库连接的开销，降低了网络传输的数据量，有助于节省系统资源。

类型

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

应用场景

数据迁移：将数据从一个数据库迁移到另一个数据库时，批量上传可以大大提高迁移速度。
数据导入：从外部文件（如 CSV、Excel）导入数据到数据库时，批量上传是常用的方法。
批量更新：在某些情况下，批量上传也可以用于批量更新数据库中的记录。

常见问题及解决方法

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

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

解决方法：

在插入前检查数据，确保没有重复值。
使用 INSERT IGNORE 或 REPLACE INTO 语句来处理重复值。

-- 使用 INSERT IGNORE
INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
...;

-- 使用 REPLACE INTO
REPLACE INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
...;

问题2：批量插入时遇到 `Data too long for column` 错误

原因：插入的数据长度超过了列的定义长度。

解决方法：

检查插入的数据，确保其长度符合列的定义。
修改列的定义，增加列的长度。

ALTER TABLE table_name MODIFY column_name VARCHAR(new_length);

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

原因：可能是由于网络延迟、数据库配置不当或数据量过大等原因导致的。

解决方法：

优化数据库配置，如调整缓冲区大小、增加连接数等。
使用 LOAD DATA INFILE 语法，该语法通常比 INSERT INTO ... VALUES 更快。
分批次插入数据，避免一次性插入过多数据导致性能问题。

示例代码

以下是一个使用 Python 和 mysql-connector-python 库进行批量插入的示例：

import mysql.connector

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

# 插入数据的 SQL 语句
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"

# 要插入的数据列表
data = [
    ('value1', 'value2', 'value3'),
    ('value4', 'value5', 'value6'),
    # ...
]

# 执行批量插入
cursor.executemany(sql, data)

# 提交事务
cnx.commit()

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