Mysql 已存在则更新，不存在就插入【ON DUPLICATE KEY UPDATE】

柔光的暖阳◎ 2023-07-07 12:49 55阅读 0赞

# ON DUPLICATE KEY UPDATE作用 #

**先声明一点，ON DUPLICATE KEY UPDATE为Mysql特有语法，这是个坑**  
语句的作用，当insert已经存在的记录时，执行Update

# 用法 #

**什么意思？举个例子：**  
user\_admin\_t表中有一条数据如下

![user\_admin\_t][user_admin_t]

表中的主键为id，现要插入一条数据，id为‘1’，password为‘第一次插入的密码’，正常写法为：

INSERT INTO user_admin_t (_id,password) 
    VALUES ('1','第一次插入的密码')

执行后刷新表数据，我们来看表中内容

![执行insert后][insert]

此时表中数据增加了一条主键’\_id’为‘1’，‘password’为‘第一次插入的密码’的记录，当我们再次执行插入语句时，会发生什么呢？

-- 执行
    INSERT INTO user_admin_t (_id,password) 
    VALUES ('1','第一次插入的密码')

[SQL]INSERT INTO user_admin_t (_id,password) 
    VALUES ('1','第一次插入的密码') 
    
    [Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

Mysql告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：

-- 在原sql后面增加 ON DUPLICATE KEY UPDATE 
    INSERT INTO user_admin_t (_id,password) 
    VALUES ('1','第一次插入的密码') 
    ON DUPLICATE KEY UPDATE 
    _id = 'UpId',
    password = 'upPassword';

我们再一次执行：

[SQL]INSERT INTO user_admin_t (_id,password) 
    VALUES ('1','第一次插入的密码') 
    ON DUPLICATE KEY UPDATE 
    _id = 'UpId',
    password = 'upPassword';
    受影响的行: 2
    时间: 0.131s

可以看到 受影响的行为2，这是因为将原有的记录修改了，而不是执行插入，看一下表中数据：

![DUPLICATE后][DUPLICATE]

原本‘id’为‘1’的记录，改为了‘UpId’，‘password’也变为了‘upPassword’，很好的解决了重复插入问题

# 扩展 #

当插入多条数据，其中不只有表中已存在的，还有需要新插入的数据，Mysql会如何执行呢？会不会报错呢？

其实Mysql远比我们想象的强大，他会智能的选择更新还是插入，我们尝试一下：

INSERT INTO user_admin_t (_id,password) 
    VALUES 
    ('1','第一次插入的密码') ,
    ('2','第二条记录')
    ON DUPLICATE KEY UPDATE 
    _id = 'UpId',
    password = 'upPassword';

运行sql

[SQL]INSERT INTO user_admin_t (_id,password) 
    VALUES 
    ('1','第一次插入的密码') ,
    ('2','第二条记录')
    ON DUPLICATE KEY UPDATE 
    _id = 'UpId',
    password = 'upPassword';
    受影响的行: 3
    时间: 0.045s

Mysql执行了一次修改，一次插入，表中数据为：

![多记录插入][SouthEast]

# VALUES修改 #

那么问题又来了，有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值，如果我想要分别给不同的记录插入不同的值怎么办呢？

INSERT INTO user_admin_t (_id,password) 
    VALUES 
    ('1','多条插入1') ,
    ('UpId','多条插入2')
    ON DUPLICATE KEY UPDATE 
    password =  VALUES(password);

方法之一可以将后面的修改条件改为VALUES(password)，动态的传入要修改的值，执行以下：

[SQL]INSERT INTO user_admin_t (_id,password) 
    VALUES 
    ('1','多条插入1') ,
    ('UpId','多条插入2')
    ON DUPLICATE KEY UPDATE 
    password =  VALUES(password);
    受影响的行: 4
    时间: 0.187s

成功的修改了两条记录，刷新一下表

![多条修改][SouthEast 1]

我们成功的为不同id的password修改成了不同的值

# 总结 #

其实修改的方法有很多种，包括SET或用REPLACE，连事务都省的做，ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求，但它是Mysql的特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。

[user_admin_t]: https://img-blog.csdn.net/20171105150445430?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[insert]: https://img-blog.csdn.net/20171105150809525?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[DUPLICATE]: https://img-blog.csdn.net/20171105151541091?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast]: https://img-blog.csdn.net/20171105153119038?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 1]: https://img-blog.csdn.net/20171105153701640?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvenliMjAxNw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast