Python使用MySQL，无记录则插入，有记录则更新 - ON DUPLICATE KEY UPDATE

一、基本语法

ON DUPLICATE KEY UPDATE 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。

使用注意事项如下：

• ON DUPLICATE KEY UPDATE语句根据主键id或唯一键来判断当前插入是否已存在。
• 记录已存在时，只会更新ON DUPLICATE KEY UPDATE之后指定的字段。
• 如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。

二、用法示例

下面基于Python语言，给出具体使用示例：

1. 在MySQL中建立 student 表，将code设置为主键，如下所示：

2. 插入基础数据：

3. 使用 ON DUPLICATE KEY UPDATE 更新数据。

假设我们要将第二条记录的name修改为Lily：

# 数据库连接
conn = pymysql.connect(
    host='172.171.xxx.yyy',
    user='admin',
    passwd='xxxxxx',
    db='xxx',
    port=3306
)

data = ['002', 'Lily']
SQL_FORMAT = """INSERT INTO student VALUES ('{0}', '{1}')  ON DUPLICATE KEY UPDATE name ='{2}' """
sql_1 = SQL_FORMAT.format(data[0], data[1], data[1])
print(sql_1)  # INSERT INTO student VALUES ('002', 'Lily')  ON DUPLICATE KEY UPDATE name ='Lily' 

with conn.cursor() as cursor:
    cursor.execute(sql_1) 
    cursor.close()
conn.commit()  # 提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查看数据库中，第二条记录中的name已经被更新为Lily，如下所示：

【核心代码】：

写法1：

data = ['002', 'Lily']
SQL_FORMAT = """INSERT INTO student VALUES ('{0}', '{1}')  ON DUPLICATE KEY UPDATE name ='{2}' """
sql_1 = SQL_FORMAT.format(data[0], data[1], data[1])
print(sql_1)  # INSERT INTO student VALUES ('002', 'Lily')  ON DUPLICATE KEY UPDATE name ='Lily' 
1
2
3
4

写法2：

data = ['002', 'Lily']
SQL_FORMAT = "INSERT INTO student VALUES ('%s', '%s') ON DUPLICATE KEY UPDATE name = '%s' "
sql_1 = SQL_FORMAT % (data[0], data[1], data[1])
print(sql_1)  # Insert  INTO student VALUES ('002', 'Lily') ON DUPLICATE KEY UPDATE name = 'Lily' 
1
2
3
4

三、补充说明

有大佬指出 ON DUPLICATE KEY UPDATE 存在严重的性能和其他隐患问题，推荐改成 先查询一次分出新增集合和修改集合，再分别进行批量新增和批量修改的方式进行，并对批量修改时使用case when的方式实现。

参考地址：为什么不建议使用ON DUPLICATE KEY UPDATE？