mysql如何用一条sql语句实现不存在就插入,存在的话则更新

时间:2021-12-30 01:02:57

今天被朋友问到一个问题,在用SQL对mysql进行操作时,如何用一条sql实现不存在则插入,存在则更新(存在是针对主键或者unique来说的)。说实话,以前比较懒,每次都直接在程序中写一个if...else...,不免觉得代码太丑陋,而且在性能上也不好。因此对这个问题跟朋友进行了深入的讨论,加上网络上的资料,对这个问题进行了一个总结。假定我们有如下的一张表,id是主键。

  Field Type  
  id int(11)  
  name varchar(20)  
  isForS tinyint(1)  
  money int(11)

mysql如何用一条sql语句实现不存在就插入,存在的话则更新

create table
CREATE TABLE `ceshi` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(20) DEFAULT NULL,
   `isForS` tinyint(1) DEFAULT NULL,
   `money` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
表中有如下数据。
id name isForS money
4 ddd 1 200
3 zhangsan 0 300
2 lisi 0 200
1 zhangsan 1 201

1.使用replace

REPLACE INTO ceshi(id,NAME,isForS,money) VALUES(1,"zhangsan",1,100),当执行这条命令是由于数据库中已经存在了该条数据(id主键重复),所以数据库中的数据没有改变。实际上当数据库中不存在要插入的数据时,replace跟insert语句具有同样的效果。而当数据库中有已经存在的数据行时,mysql先把以前的记录删除掉,接着再执行insert.

2.使用dual虚拟表

当我们在mysql中输入命令select 1+1时,实际上就是select 1+1 from dual。dual这个表不过是让你的sql语句写起来更符合传统的sql语句的格式。我们可以这样来写我们的sql语句,INSERT INTO ceshi SELECT 1,"zhang",0,200 FROM DUAL WHERE NOT EXISTS (SELECT * FROM ceshi WHERE id=1) 。

3.使用ON DUPLICATE KEY UPDATE

执行如下的sql语句,INSERT INTO ceshi VALUE(1,"zhang",1,100) ON DUPLICATE KEY UPDATE money=money+100

如果数据库中不存在id为1的记录,则相当于执行INSERT INTO ceshi VALUE(1,"zhang",1,100),此时返回的受影响的行数为1.

如果数据库中存在相应的记录,相当于执行update ceshi set money=money+100 where id=1.此时返回的受影响的行数为2.

4.使用ignore关键字

INSERT IGNORE INTO ceshi VALUE(1,"zhang",1,100),当执行这条sql语句时,如果数据库中已经存在相同的记录,则数据库会忽略该条命令,返回的受影响的行数为0.