教你Mysql如何实现不存在则插入,存在则更新

素颜马尾好姑娘i 2023-09-29 07:27 13阅读 0赞

**数据准备**

![3f8f8eae61ac7884c0ba8416d879b0c4.png][]

**ON DUPLICATE KEY UPDATE**

insert into test_table(id,username)
    VALUES(4,'fukaiit')
    ON DUPLICATE KEY UPDATE username='fukaiit'

执行如上sql，该id不存在(此处id不存在，指的是id为X的记录不存在，包括主键、unique索引列等情况)，相当于执行了insert部分，普通插入，受影响的行：1。

insert into test_table(id,username)
    VALUES(1,'fukaiit')

ON DUPLICATE KEY UPDATE username='fukaiit'  
执行如上sql，该id已存在，相当于执行了update部分，受影响的行：2。

执行结果如下，注意只是更新username，是保留了email的。

![d096c3b15477fb13dc04a4d48fcab8bf.png][]

**REPLACE INTO … VALUES…**

REPLACE INTO test_table(id,username)
    VALUES(5,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

REPLACE INTO test_table(id,username)
    VALUES(1,'fukaiit');

执行如上sql，该id已存在，相当于执行了删除再插入的操作，受影响的行：2。

执行结果如下，注意删除后再插入是没有email值的，所以此时email为null，注意与ON DUPLICATE KEY UPDATE的区别。

![4259ad793a9973a5b288223eec2e6ed8.png][]

无论是insert on duplicate key update还是replace into，在插入时MySQL返回的影响行数就是插入的记录数，但是在更新时返回的影响行数是更新行数\*2。

**INSERT IGNORE INTO … VALUES…**

INSERT IGNORE INTO test_table(id,username) 
    values(6,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

INSERT IGNORE INTO test_table(id,username) 
    values(1,'fukaiit_new');

执行如上sql，该id已存在，忽略了该插入操作，数据没有变化，受影响的行：0。

**使用DUAL虚表和NOT EXISTS**

该方法与INSERT IGNORE INTO ... VALUES...方法的效果相同。

INSERT INTO test_table(id,username)
    SELECT 7,'fukaiit' from DUAL 
    where NOT EXISTS (select * from test_table where id=7);

执行如上sql，该id不存在，select子句返回结果集为空，NOT EXISTS成立，相当于执行了insert，普通插入，受影响的行：1。

INSERT INTO test_table(id,username)
    SELECT 2,'fukaiit' from DUAL 
    where NOT EXISTS (select * from test_table where id=2);

执行如上sql，该id已存在，select子句返回结果集不为空，NOT EXISTS不成立，则未执行插入操作，数据没有变化，受影响的行：0。

以上为个人经验，希望能给大家一个参考

[3f8f8eae61ac7884c0ba8416d879b0c4.png]: https://img-blog.csdnimg.cn/img_convert/3f8f8eae61ac7884c0ba8416d879b0c4.png
[d096c3b15477fb13dc04a4d48fcab8bf.png]: https://img-blog.csdnimg.cn/img_convert/d096c3b15477fb13dc04a4d48fcab8bf.png
[4259ad793a9973a5b288223eec2e6ed8.png]: https://img-blog.csdnimg.cn/img_convert/4259ad793a9973a5b288223eec2e6ed8.png