Bill: MySQL exists this record is updated, does not exist the SQL that inserted the record

A MySQL tutorial exists that the record is updated, the SQL that does not exist is inserted into the record

INSERT Table Values (1, ' yourname') on DUPLICATE KEY UPDATE auto_name= 'yourname'

On DUPLICATE KEY Update usage

If you specify an on DUPLICATE KEY update and the insert row causes duplicate values to appear in a unique index or primary KEY, the old line UPDATE is performed. For example, if 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)  -  onDUPLICATEKEY UPDATEC=C+1; MySQL> UPDATE Table SETC=C+1 WHEREA=1;

If the row is inserted as a new record, the value of the affected row is 1, and if the original record is updated, the value of the affected row is 2.

NOTE: If column B is also the only column, the insert is equivalent to this UPDATE statement:

MySQL>UPDATEtableSET c=c+1WHERE a  =1OR b=21;

If A=1 OR b=2 matches multiple rows, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on a table with multiple unique keywords.

You can use the values (col_name) function from the Insert ... in the UPDATE clause. The insert portion of the UPDATE statement refers to the column value. In other words, if a duplicate keyword conflict does not occur, values (col_name) in the update clause can refer to the value of the col_name being inserted. This function is especially useful for multi-row insertions. The VALUES () function is only in the insert ... The UPDATE statement makes sense, and returns null at other times.

Example:  

 mysql>  insert  into  table  (a,b,c) values  (1 , 2 , 3 ), (4 , 5 ,  6  )  ->  on  DUPLICATE key  =  values  (a) +  values  (b); 

This statement works the same as the following two statements:

Mysql> INSERT  into Table(A,B,C)VALUES(1,2,3)  -  onDUPLICATEKEY UPDATEC=3; MySQL> INSERT  into Table(A,B,C)VALUES(4,5,6)  -  onDUPLICATEKEY UPDATEC=9;

When you use the on DUPLICATE KEY update, the delayed option is ignored.

--------------------------------------------------------------------------------------------------------------- ---------

MySQL has a special construct for this. Assume the ' username ' column below is UNIQUE:

INSERT INTO users (username, email) VALUES (‘Jo‘, ‘[email protected]‘)
ON DUPLICATE KEY UPDATE email = ‘[email protected]‘

The ' on DUPLICATE key ' statement only works on PRIMARY key and UNIQUE columns

Bill: MySQL exists this record is updated, does not exist the SQL that inserted the record