mysql batchinsert 主键冲突覆盖

原创

mob64ca12d9b014 2024-11-05 11:53:09 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12d9b014的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 批量插入与主键冲突覆盖的解决方案

在实际的应用开发中，批量插入数据是一个常见的操作。尤其是在数据迁移、大量数据导入等场景中，如何高效地插入数据成为了一个需要考虑的重要问题。然而，在进行批量插入时，常常会遇到主键冲突的问题。这种情况下如何处理主键冲突，使得数据插入既能覆盖已有记录，又能保留新数据的增量更新，将成为我们的重点讨论内容。

数据库设计

我们首先假设有一个名为 users 的表，表的结构如下：

字段名	数据类型	描述
id	INT PRIMARY KEY	用户ID（主键）
name	VARCHAR(255)	用户名称
age	INT	用户年龄
email	VARCHAR(255)	用户邮箱

我们将使用这个表来演示如何处理批量插入时的主键冲突。

批量插入与主键冲突

在 MySQL 中，我们可以使用 INSERT ... ON DUPLICATE KEY UPDATE 语法来处理主键冲突。该语法的作用是在主键冲突时更新已有记录，而不是简单地抛出错误。

示例代码

假设我们想要插入一批用户数据。以下是实现的代码示例：

INSERT INTO users (id, name, age, email) VALUES
(1, 'Alice', 25, 'alice@example.com'),
(2, 'Bob', 30, 'bob@example.com'),
(3, 'Charlie', 35, 'charlie@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age),
email = VALUES(email);

在上面的代码中，假设 users 表中已有 ID 为 1 或 2 的记录。如果我们再尝试插入这些记录，则会导致主键冲突，系统会执行 ON DUPLICATE KEY UPDATE 后面的内容，更新相应的用户信息。

性能优化

在进行批量操作时，性能优化是必不可少的。以上的插入方式在处理大量数据时可能效率不高。为了提高性能，我们可以根据具体的业务需求，选择不同的策略。

1. 分批插入

将数据分成小批次进行插入，可以有效减少锁争用的情况。以下是将1000条记录分成10个批次进行插入的示例：

def batch_insert_users(users_data):
    batch_size = 100
    for i in range(0, len(users_data), batch_size):
        batch = users_data[i:i + batch_size]
        sql = "INSERT INTO users (id, name, age, email) VALUES %s ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age), email=VALUES(email);"
        
        cursor.execute(sql, batch)

2. 使用事务

使用数据库事务可以保证操作的原子性，这意味着插入时要么全成功，要么全失败。以下是使用事务的示例代码：

try:
    connection.start_transaction()

    # 批量插入操作
    cursor.executemany(sql, user_data)

    connection.commit()  # 提交事务
except Exception as e:
    connection.rollback()  # 回滚事务
    print(f"Error occurred: {e}")

类图示例

以下是一个简单的类图，展示了用户相关的数据模型和操作。

classDiagram
    class User {
        +int id
        +String name
        +int age
        +String email
        +void save()
        +void update()
        +void delete()
    }
    class UserRepository {
        +void batchInsert(users: List<User>)
        +void findByID(id: int)
        +void findAll()
    }
    UserRepository --> User