The record is updated if mysql exists and sql of the record is inserted if it does not

  • 2020-05-09 19:28:53
  • OfStack


INSERT table (auto_id, auto_name) values (1,  ' yourname') ON DUPLICATE KEY UPDATE auto_name='yourname'


The use of ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and inserting a row results in a duplicate value in either an UNIQUE index or PRIMARY KEY, the old row UPDATE is executed. For example, if the column a is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:

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; If the original record is updated, the value of the affected row is 2.
Note: if column b is also the only column, then INSERT is equivalent to this UPDATE statement:

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; 

If a=1 OR b=2 matches multiple row directions, only 1 row is updated. In general, you should try to avoid using the ON DUPLICATE KEY clause for tables with multiple 1-only keywords.
You can use the VALUES(col_name) function in the UPDATE clause from INSERT... The INSERT part of the UPDATE statement references the column values. In other words, VALUES(col_name) in the UPDATE clause can reference the inserted value of col_name if there is no duplicate keyword conflict. This function is especially useful for multi-row inserts. VALUES() function only in INSERT... It makes sense in the UPDATE statement, and returns NULL at other times.
Example:

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:

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.

Related articles: