mysql中插入记录时不存在时插入已经存在则更新

有很多朋友都在问关于mysql中插入记录时不存在时插入已经存在则更新这个问题怎么操作,下面我来介绍介绍:ON DUPLICATE KEY UPDATE的使用.

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE,例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果,代码如下:

1. mysql>INSERTINTOtable(a,b,c)VALUES(1,2,3)
2. ->ONDUPLICATEKEYUPDATEc=c+1;
3. mysql>UPDATEtableSETc=c+1WHEREa=1;

实例1,代码如下:

1. mysql>truncate`200702`;
2. QueryOK,0rowsaffected(0.01sec)
4. mysql>select*from`200702`;
5. Emptyset(0.01sec)
7. mysql>insertinto`200702`(`domain`,`2nd_domain`,`tld`,`query_ns1`,`query_ns2`,`report_date`)
9. values('dnspod.com','dnspod','com',1000,2000,'2007-02-04')ONDUPLICATEKEYUPDATE`query_ns1`=
11. `query_ns1`+1000,`query_ns2`=`query_ns2`+2000;
12. QueryOK,1rowaffected(0.00sec)
14. mysql>select*from`200702`;
15. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
16. |id|domain|2nd_domain|tld|query_ns1|query_ns2|query_ns3|query_ns4|report_date|
17. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
18. |1|dnspod.com|dnspod|com|1000|2000|0|0|2007-02-04|
19. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
20. 1rowinset(0.00sec)
22. mysql>insertinto`200702`(`domain`,`2nd_domain`,`tld`,`query_ns1`,`query_ns2`,`report_date`)
24. values('dnspod.com','dnspod','com',1000,2000,'2007-02-04')ONDUPLICATEKEYUPDATE`query_ns1`=
26. `query_ns1`+1000,`query_ns2`=`query_ns2`+2000;
27. QueryOK,2rowsaffected(0.01sec)
29. mysql>select*from`200702`;
30. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
31. |id|domain|2nd_domain|tld|query_ns1|query_ns2|query_ns3|query_ns4|report_date|
32. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
33. |1|dnspod.com|dnspod|com|2000|4000|0|0|2007-02-04|
34. +----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
35. 1rowinset(0.01sec)
36. --phpfensi.com
37. mysql>

当然,建表的时候,千万别忘记了给domain做一个unique,代码如下:

UNIQUE KEY `domain` (`domain`,`report_date`)

key存在则更新,不存在则插入,代码如下:

insert into .... on duplicate key update

对于需要根据原记录进行操作的.如表中icount字段用于计数,当没有记录时,插入的value为0,当有记录时,value需要更新为value+1,这时replace就不能完成这个功能.使用insert则可以,其基本语法是insert into ... on duplicate

key update...,如上述语句为,代码如下:

insert into t_test set ikey=1,value='a',value2=1 on duplicate key update value2=value2+1;

如果表中有多个唯一索引,如对value字段加个unique key.这时表中有ikey和value两个唯一索引,replace会把所有与其唯一索引值相同的数据项删除,再插入新记录.如表中有两个记录,代码如下:

1. +------+-------+--------+
2. |ikey|value|icount|
3. +------+-------+--------+
4. |2|a|10|
5. |1|b|40|
6. +------+-------+--------+

则replace into t_test set ikey=1,value='a',icount=0;会把表中的两条记录都删除,然后插入新记录.而insert into t_test set ikey=1,value='a',icount=0 on duplicate key update icount=icount+1则只更新一条记录.其效果相当于如下代码:

update t_test set icount=icount+1 where ikey=1 or value='a' limit 1;