A MySQL tutorial exists The record is updated, and the SQL that is inserted into the record does not exist
INSERT table (auto_id, Auto_name) VALUES (1, ' yourname ') on DUPLICATE KEY UPDATE auto_name= ' yourname '
Using on DUPLICATE KEY update
If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:
Copy code code as follows:
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;
If the row is inserted as a new record, the value of the affected row is 1, and if the existing record is updated, the value of the affected row is 2.
NOTE: If column B is also a unique column, insert is equivalent to this UPDATE statement:
Copy code code as follows:
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.
You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement and returns null at other times.
Example:
Copy code code as follows:
mysql> INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6)
-> on DUPLICATE KEY UPDATE c=values (a) +values (b);
This statement acts the same as the following two statements:
Copy code code as follows:
mysql> INSERT into table (a,b,c) VALUES (1,2,3)
-> on DUPLICATE KEY UPDATE c=3;
mysql> INSERT into table (a,b,c) VALUES (4,5,6)
-> on DUPLICATE KEY UPDATE c=9;
When you use on DUPLICATE KEY update, the delayed option is ignored.