If primary key conflicts are updated otherwise insert (on DUPLICATE key update)

INSERT into User_tag_exp (uid,tag,detail) VALUES (?,?,?) On DUPLICATE KEY UPDATE detail=.

See the program actually found that MySQL has this function.

Previously written procedures ah ................

MySQL has been supporting inserts since version 4.1 ... On DUPLICATE KEY UPDATE syntax, which requires 3 SQL statements (select,insert,update) to be executed, and reduced to 1 statements to complete.

INSERT ... On DUPLICATE key Update, when the inserted record throws a primary key conflict or violates a unique constraint, updates the old record with update or inserts a new record.

For example, the Ipstats table structure is as follows: CREATE table Ipstats (IP VARCHAR () not nullunique,clicks SMALLINT (5) Unsignednot nulldefault ' 0 ');

3 SQL statements would have been executed, as follows: IF (SELECT * from ipstats WHERE ip= ' 192.168.0.1 ') {UPDATE ipstats SET clicks=clicks+1where ip= ' 192.168.0.1 ';} else {insertinto ipstats (IP, clicks) VALUES (' 192.168.0.1 ', 1);}

The following 1 SQL statements are now complete: Insertinto ipstats VALUES (' 192.168.0.1 ', 1) on DUPLICATE KEY UPDATE clicks=clicks+1;

Note that to use this statement, the condition is that the table must have a unique index or primary key.

Look at one more example:
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| UID | Int (11) | NO |         PRI |       | |
| uname | varchar (20) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in Set (0.00 sec)

Mysql> select * from test;
+-----+--------+
| UID | uname |
+-----+--------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | Me |
+-----+--------+
3 Rows in Set (0.00 sec)

Mysql> INSERT into test values (3, ' insertname ')
-> on DUPLICATE KEY UPDATE uname= ' updatename ';
Query OK, 2 rows affected (0.03 sec)

Mysql> select * from test;
+-----+------------+
| UID | uname |
+-----+------------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | UpdateName |
+-----+------------+
3 Rows in Set (0.00 sec)

Mysql> CREATE index I_test_uname on test (uname);
Query OK, 3 rows affected (0.20 sec)
Records:3 duplicates:0 warnings:0

mysql> INSERT into Test VALUES (1, ' uname2 ')
-> on DUPLICATE KEY UPDATE uname= ' update2records ';
Query OK, 2 rows Affected (0.00 sec)

Mysql> select * from test;
+-----+----------------+
| UID | uname |
+-----+----------------+
| 2 | uname2 |
| 1 | Update2records |
| 3 | UpdateName |
+-----+----------------+
3 Rows in Set (0.00 sec)

When inserted, it conflicts with two records, which are caused by primary keys and unique indexes, respectively. But eventually only one of them was update. This is also illustrated in the manual, where it is not recommended to have multiple unique indexes (or a unique index with keys).

From: Http://hi.baidu.com/wxj_man/item/218f01952c106f5ef14215e7