MySQL Insert update _on DUPLICATE KEY update

Premise: The action table has a primary key or a unique index

INSERT INTO: The table does not have a corresponding record, then inserted, if there is a corresponding record, then error;

INSERT INTO IGNORE: The corresponding record does not exist in the table, it is inserted, if there is a corresponding record, it is ignored and no action is made

REPLACE into: The table does not have a corresponding record, then insert, if there is a corresponding record, delete the original record, and then insert a new record.

On DUPLICATE key update (on DUPLICATE key update) functions like replace into:

Insert a new record directly if there is no data in the table (primary key value or unique index value for the record to be inserted);

If the data already exists in the table (the primary key or unique index value is the same), the update operation is performed.

So, on DUPLICATE KEY update and replace into are different during operation:

When data already exists, on DUPLICATE KEY Update performs an update operation instead of deleting the original record before inserting a new record.

Statement: INSERT into table_name (field1,field2 ...) VALUES (value1,value2 ...) on duplicate key update filed3=value3;

Description: The statement after update can be another expression.

Suppose you have one of the following table:

Mysql>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli |3600||4| Xiaohua |6000|+----+----------+-------+4Rowsinch Set(0.00sec) MySQL>describe Staff_3;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID |int( One)  | NO | PRI | NULL | auto_increment | | name |Char(Ten) | YES | UNI |                NULL | || Slary |int( One)  |     YES | |                NULL | |+-------+----------+------+-----+---------+----------------+3Rowsinch Set(0.00Sec

On the duplicate key on command: (as can be seen from the ID, the command performs the update operation do not waste the self-increment property value)

mysql> INSERT INTO Staff_3 (name,slary) VALUES ('Xiaoli',4000) on duplicate key update slary= the; Query OK,2Rows Affected (0.00sec) MySQL>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli | the||4| Xiaohua |6000|+----+----------+-------+4Rowsinch Set(0.00sec) MySQL> INSERT into Staff_3 (name,slary) VALUES ('xiaoming',4000) on duplicate key update slary= the; Query OK,1Row affected (0.00sec) MySQL>Select* fromStaff_3;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|1| liding |2700||2| Haofugui |3500||3| Xiaoli | the||4| Xiaohua |6000||5| Xiaoming |4000|+----+----------+-------+5Rowsinch Set(0.00Sec

MySQL Insert update _on DUPLICATE KEY update