mysql 批量写入表

基础概念

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

优势

性能提升：批量写入减少了与数据库的交互次数，降低了网络开销和数据库负载，从而提高了数据插入的速度。
减少锁竞争：在高并发环境下，批量写入可以减少对表的锁定时间，降低锁竞争的概率。
简化代码：通过一次操作插入多条记录，简化了代码逻辑，提高了开发效率。

类型

MySQL 提供了多种批量写入的方式，包括：

使用 INSERT INTO ... VALUES 语法：

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1, value2, ...),
    (value3, value4, ...),
    ...
    (valueN, valueM, ...);

使用 INSERT INTO ... SELECT 语法：

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

应用场景

批量写入适用于以下场景：

数据导入：从外部文件或其他数据库导入大量数据时，使用批量写入可以显著提高效率。
日志记录：在系统运行过程中，需要记录大量日志信息时，批量写入可以减少对数据库的频繁访问。
批量操作：在进行批量更新、插入或删除操作时，使用批量写入可以提高性能。

遇到的问题及解决方法

问题1：批量写入时出现语法错误

原因：可能是 SQL 语句格式不正确，或者在插入的数据中包含了非法字符。

解决方法：

检查 SQL 语句的格式是否正确，确保每个括号、逗号和引号都匹配。
检查插入的数据是否包含非法字符，如 SQL 注入攻击字符等。

问题2：批量写入时性能不佳

原因：

数据量过大，导致单次批量写入操作耗时较长。
数据库服务器性能不足，无法处理大量的并发请求。

解决方法：

分批次进行批量写入，避免一次性插入过多数据。
优化数据库服务器配置，如增加内存、升级 CPU 等。
使用数据库连接池和事务管理，减少连接开销和提高并发处理能力。

问题3：批量写入时出现主键冲突

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

解决方法：

在插入数据前，检查数据中是否存在重复的主键值，并进行去重处理。
使用 INSERT IGNORE 或 REPLACE INTO 语法来处理主键冲突。

-- 使用 INSERT IGNORE 忽略主键冲突
INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES
    (value1, value2, ...),
    (value3, value4, ...),
    ...
    (valueN, valueM, ...);

-- 使用 REPLACE INTO 替换主键冲突的记录
REPLACE INTO table_name (column1, column2, ...)
VALUES
    (value1, value2, ...),
    (value3, value4, ...),
    ...
    (valueN, valueM, ...);

示例代码

以下是一个使用 Python 和 MySQL 进行批量写入的示例代码：

import mysql.connector

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

cursor = db.cursor()

# 批量插入数据
sql = "INSERT INTO table_name (column1, column2) VALUES (%s, %s)"
val = [
    ('value1', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6')
]

cursor.executemany(sql, val)

# 提交事务
db.commit()

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