MySQL 批量插入主键冲突

引言

在使用MySQL数据库进行数据插入时，经常会遇到主键冲突的问题。当我们尝试向一个已经存在相同主键值的表中插入数据时，MySQL会抛出主键冲突的异常，导致插入操作失败。解决这个问题的一个常见方法是使用批量插入语句，将多个数据一次性插入到表中。本文将介绍如何使用MySQL的批量插入语句来处理主键冲突。

批量插入语句

批量插入语句是一种将多个数据一次性插入到表中的高效方式。相比于逐条插入数据，批量插入可以减少网络开销，提高插入数据的效率。在MySQL中，可以使用INSERT INTO ... VALUES语句来实现批量插入。

下面是一个使用批量插入语句的示例：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
       (value4, value5, value6),
       (value7, value8, value9);

以上语句将一次性向table_name表中插入三条数据，每条数据由(column1, column2, column3)表示，对应的值分别是(value1, value2, value3)、(value4, value5, value6)和(value7, value8, value9)。

主键冲突处理

当使用批量插入语句时，如果存在主键冲突，MySQL会抛出Duplicate entry 'value' for key 'PRIMARY'的异常，其中value表示引发冲突的主键值。

为了避免主键冲突导致整个插入操作失败，我们可以使用INSERT IGNORE或者INSERT INTO ... ON DUPLICATE KEY UPDATE语句。

INSERT IGNORE

INSERT IGNORE语句在插入数据时，如果发生主键冲突，将会忽略冲突的数据，继续插入其他数据。

下面是一个使用INSERT IGNORE语句处理主键冲突的示例：

INSERT IGNORE INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
       (value4, value5, value6),
       (value7, value8, value9);

当存在主键冲突时，MySQL会忽略冲突的数据，继续插入其他数据。

INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO ... ON DUPLICATE KEY UPDATE语句在插入数据时，如果发生主键冲突，可以选择更新已存在的数据。

下面是一个使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句处理主键冲突的示例：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
       (value4, value5, value6),
       (value7, value8, value9)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3;

当存在主键冲突时，MySQL会更新已存在的数据，更新的列和对应的值由ON DUPLICATE KEY UPDATE子句指定。

示例代码

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

import mysql.connector

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

# 批量插入数据
data = [
    ('value1', 'value2', 'value3'),
    ('value4', 'value5', 'value6'),
    ('value7', 'value8', 'value9')
]

try:
    # 使用INSERT IGNORE语句插入数据
    cursor.executemany("""
        INSERT IGNORE INTO table_name (column1, column2, column3)
        VALUES (%s, %s, %s)
    """, data)
    
    # 使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句插入数据
    cursor.executemany("""
        INSERT INTO table_name (column1, column2, column3)
        VALUES (%s, %s,