这个例子其实是非常的简单了就是要实现sql插入数据时,如果已经存在,则执行update更新了,这样对于sql与程序来讲是非常的简洁的,下面就和小编一起来深入的分析一下吧．
在很多项目中,我们需要对数据进行不断的调用和更新,如果有新的数据过来,还要把它加入到数据库中。其中非常重要的一种情况就是，我们不知道传过来的数据是不是原本数据库中就已经有了的记录,所以我们常常需要先通过抓取数据，判断是否存在,如果存在执行update,如果不存在执行insert,这样就要进行两次数据库操作,第一次是查询，第二次是更新或插入,有没有一种方法可以只需要执行一次操作即可呢？答案是有的。
INSERT ... ON DUPLICATE KEY UPDATE 方法可以帮助我们非常好的解决这一问题。让我们来看一个例子：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=b-1,c=c+1;
这一个语句就可以实现,在插入(1,2,3)这条记录的时候,检查是否存在a=1，如果有a=1的记录,那么更新a=1这条记录,相当于执行了一次下面这个语句:
UPDATE table SET b=b-1,c=c+1 WHERE a=1;
如果数据库中不存在a=1这条记录,那么就插入新的记录.
使用INSERT ... ON DUPLICATE KEY UPDATE的前提是,a字段被设置为“唯一键”索引，否则该方法是无效的.
再看一些例子:
该语句是基于唯一索引或主键使用,比如一个字段a被加上了unique index,并且表中已经存在了一条记录值为1,下面两个语句会有相同的效果:
- INSERTINTOtable(a,b,c)VALUES(1,2,3)
- ONDUPLICATEKEYUPDATEc=c+1;
- UPDATEtableSETc=c+1WHEREa=1;
ON DUPLICATE KEY UPDATE后面可以放多个字段,用英文逗号分割,使用ON DUPLICATE KEY UPDATE,最终如果插入了一个新行,则受影响的行数是1,如果修改了已存在的一行数据,则受影响的行数是2.
如果字段b也被加上了unique index,则该语句和下面的update语句是等效的:
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2匹配了多行，则只有一行会被修改。通常的，在ON DUPLICATE KEY UPDATE语句中，我们应该避免多个唯一索引的情况。如果需要插入或更新多条数据，并且更新的字段需要根据其它字段来运算时，可以使用如下语句：
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
在ON DUPLICATE KEY UPDATE后面使用VALUES()方法,这个语句等同于下面的两个语句:
- INSERTINTOtable(a,b,c)VALUES(1,2,3)
- ONDUPLICATEKEYUPDATEc=3;--1+2
- INSERTINTOtable(a,b,c)VALUES(4,5,6)
- ONDUPLICATEKEYUPDATEc=9;--4+5
- --phpfensi.com
如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果通过INSERT ... ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是4。如果想解决该问题，可以通过如下语句：
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
重点是这句id=LAST_INSERT_ID(id),还有一种方法是使用ignore,我们来看一个例子:
INSERT ignore INTO a(id, type) VALUES ( 11, 22)
这个语句中使用了ignore,意思是:如果数据库中存在一条记录id=11,那么就不执行insert操作(忽略),只有上述条件不满足时才执行插入操作,ignore方法也要求这里的id为唯一键(主键默认就是是唯一键,因此id可以是主键).
另外,还有一种方法是replace into,它的使用方法和insert into一样,但是和上面的ignore效果不同,如果数据库中已经存在id=11,那么强制替换id=11这条记录的type为22.
看个例子:下面通过代码说明之间的区别,如下:
- createtabletesttb(
- idintnotnullprimarykey,
- namevarchar(50),
- ageint
- );
- insertintotesttb(id,name,age)values(1,"bb",13);
- select*fromtesttb;
- insertignoreintotesttb(id,name,age)values(1,"aa",13);
- select*fromtesttb;//仍是1，“bb”,13，因为id是主键，出现主键重复但使用了ignore则错误被忽略
- replaceintotesttb(id,name,age)values(1,"aa",12);
- select*fromtesttb;//数据变为1,"aa",12
总结一下:如果要实现插入数据时检查是否已经存在某个唯一键的数据,如果存在,则替换该记录的其他字段,我们可以使用三种方法来实现插入数据时判断是否存在对应键的记录,分别是INSERT ... ON DUPLICATE KEY UPDATE、insert gnore into和replace into,其中INSERT ... ON DUPLICATE KEY UPDATE和replace into可以实现如果已经存在对应键的记录时,替换该记录的其他字段.
