mysql批量数据

基础概念

MySQL 批量数据操作指的是在一次数据库交互中处理多条数据记录。这种操作可以显著提高数据处理的效率，因为它减少了与数据库服务器之间的通信次数。批量操作可以应用于插入、更新、删除等多种数据操作。

优势

性能提升：通过减少网络往返次数，批量操作可以显著提高数据处理速度。
资源优化：减少了数据库服务器的负载，提高了资源利用率。
事务一致性：在批量操作中，可以更容易地实现事务的原子性，确保数据的一致性。

类型

批量插入：一次性插入多条记录。
批量更新：一次性更新多条记录。
批量删除：一次性删除多条记录。

应用场景

数据迁移：将大量数据从一个数据库迁移到另一个数据库。
数据初始化：在系统上线时批量插入初始数据。
数据清理：定期批量删除过期或无效的数据。
数据同步：在不同系统之间同步数据。

示例代码

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

import mysql.connector

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

cursor = db.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')
]

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

# 提交事务
db.commit()

print(cursor.rowcount, "记录插入成功。")

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

参考链接

常见问题及解决方法

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

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

解决方法：

检查插入的数据，确保其长度不超过列定义的长度。
如果需要，可以修改列的定义以容纳更长的数据。

ALTER TABLE customers MODIFY COLUMN address VARCHAR(255);

问题：批量更新时遇到“Lock wait timeout exceeded”错误

原因：更新操作等待锁的时间超过了设定的超时时间。

解决方法：

优化查询，减少锁的持有时间。
调整锁等待超时时间。

SET GLOBAL innodb_lock_wait_timeout = 120; -- 设置为120秒

问题：批量删除时遇到“Deadlock found when trying to get lock”错误

原因：多个事务相互等待对方释放锁，导致死锁。

解决方法：

优化事务逻辑，减少锁的持有时间。
使用 RETRY 机制重试事务。

import time

for i in range(3):  # 最多重试3次
    try:
        cursor.execute("DELETE FROM customers WHERE status = 'inactive'")
        db.commit()
        break
    except mysql.connector.errors.DatabaseError as err:
        if err.errno == 1213:  # 死锁错误
            time.sleep(1)  # 等待1秒后重试
            db.rollback()
        else:
            raise

通过以上方法，可以有效解决 MySQL 批量数据操作中常见的问题。