On DUPLICATE KEY update syntax analysis in MySQL

[on DUPLICATE KEY update in the arena]

If the table is not insert, if so, update will need to use the MySQL on DUPLICATE KEY update syntax, so you can write less if statements in the code to judge the

The on DUPLICATE KEY update is specified at the end of the INSERT statement, and when the row is inserted causes a duplicate value to occur in a unique index or primary key, the row in which the duplicate value occurs is executed; If the problem does not cause the unique value column to repeat , the new row is inserted.

[Example explanation]

Table structure
CREATE TABLE ' Spk_goods ' (
' GID ' int (one) not NULL auto_increment COMMENT ' Cargo id ',
' CID ' int (one) not NULL COMMENT ' belongs to Category ID ',
' Name ' char ' is not NULL COMMENT ' cargo name ',
PRIMARY KEY (' gid '),
UNIQUE KEY ' NewIndex1 ' (' name ')
) Engine=innodb auto_increment=6 DEFAULT Charset=utf8

Existing table records

So the problem is, there is a need. Require inserting a new cargo. If the goods already exist in the table, add a suffix new to the name of the goods on the previous basis; if the goods do not exist, insert them into the database, complete with a single SQL

Test one: Insert existing records in a table

SQL is as follows:
INSERT into ' spk_goods ' (' CID ', ' name ')
VALUES (' 1 ', ' apple ')
On DUPLICATE KEY UPDATE

' Name ' = CONCAT (' name ', ' new ')

Execute SQL view Record changes

Test two: Insert records that are not in the table

SQL is as follows:

INSERT into ' spk_goods ' (' CID ', ' name ')
VALUES (' 2 ', ' Wathermelon ')
On DUPLICATE KEY UPDATE
' Name ' = CONCAT (' name ', ' new ')

Execute SQL view Record changes

For example, field A is defined as unique, and records (2,2,9) and (3,2,1) already exist in the original database tables table, and if a value of the inserted record repeats with the original record, the original record is updated, or the new row is inserted:
Copy code code as follows:

INSERT into TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
On DUPLICATE KEY UPDATE b=values (b);

Execution of the above SQL statement discovers a unique value conflict with the original record (2,2,9) in (2,5,7), executes on DUPLICATE KEY update, updates the original record (2,2,9) to (2,5,9), Updates (3,2,1) to (3,3,1), Insert new Record (1,2,3) and (4,8,2)