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.