如果您指定了ON DUPLICATE KEY UPDATE,並且插入行後會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值,則執
行舊行UPDATE。例如,如果列a被定義為UNIQUE,並且包含值1,則以下兩個語句具有相同的效果:
代碼如下:
代碼如下 復制代碼mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
-> ON DUPLICATE KEY UPDATE c=c+1;
mysql> UPDATE table SET c=c+1 WHERE a=1;
實例1
mysql> truncate `200702`;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from `200702`;
Empty set (0.01 sec)
mysql> insert into `200702` (`domain`, `2nd_domain`, `tld`, `query_ns1`, `query_ns2`, `report_date`)
values ('dnspod.com', 'dnspod', 'com', 1000, 2000, '2007-02-04') ON DUPLICATE KEY UPDATE `query_ns1` =
`query_ns1` + 1000, `query_ns2` = `query_ns2` + 2000;
Query OK, 1 row affected (0.00 sec)
mysql> select * from `200702`;
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
| id | domain | 2nd_domain | tld | query_ns1 | query_ns2 | query_ns3 | query_ns4 | report_date |
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
| 1 | dnspod.com | dnspod | com | 1000 | 2000 | 0 | 0 | 2007-02-04 |
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
1 row in set (0.00 sec)
mysql> insert into `200702` (`domain`, `2nd_domain`, `tld`, `query_ns1`, `query_ns2`, `report_date`)
values ('dnspod.com', 'dnspod', 'com', 1000, 2000, '2007-02-04') ON DUPLICATE KEY UPDATE `query_ns1` =
`query_ns1` + 1000, `query_ns2` = `query_ns2` + 2000;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from `200702`;
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
| id | domain | 2nd_domain | tld | query_ns1 | query_ns2 | query_ns3 | query_ns4 | report_date |
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
| 1 | dnspod.com | dnspod | com | 2000 | 4000 | 0 | 0 | 2007-02-04 |
+----+------------+------------+------+-----------+-----------+-----------+-----------+-------------+
1 row in set (0.01 sec)
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會把所有與
其唯一索引值相同的數據項刪除,再插入新記錄.如表中有兩個記錄
代碼如下 復制代碼+------+-------+--------+
| ikey | value | icount |
+------+-------+--------+
| 2 | a | 10 |
| 1 | b | 40 |
+------+-------+--------+
則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;