Mysql实现不存在则插入，存在则更新

电玩女神 2022-04-04 10:44 341阅读 0赞

## 0. 数据准备 ##

![在这里插入图片描述][20181216231620906.png]

## 1. `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的。  
![在这里插入图片描述][20181216232244598.png]

## 2. `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的区别。  
![在这里插入图片描述][20181216232625954.png]

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

## 3. 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。

## 4. 使用`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。

> References  
> [mysql如何用一条sql语句实现不存在就插入，存在的话则更新][mysql_sql]

[20181216231620906.png]: /images/20220404/4922aae4f0774284825334195229a267.png
[20181216232244598.png]: /images/20220404/ddb2b747411c47f485d7929b104c83b3.png
[20181216232625954.png]: /images/20220404/7bb592896e844f4dbec2c18369b8ff97.png
[mysql_sql]: https://blog.csdn.net/xusheng__zhang/article/details/78023578