MySQL replace into and insert ... On DUPLICATE KEY Update usage

The use of REPLACE into is similar to insert, and ultimately the purpose in the table is to insert a new row of data. The difference is that when a primary key or a unique index conflict occurs when inserting, the original record is deleted and the new record is reinserted. Therefore, using replace into makes no sense unless the table has a primary key or a unique index.

The following creates a new table for testing and adds a trigger view of how replace into works:

CREATE TABLE ' Replace_into ' (' id ' int (one) not null auto_increment, ' uid ' int (one) ' NOT null, ' name ' char (TEN) CHARACTER S ET UTF8 not NULL, PRIMARY key (' id '), UNIQUE key ' Unique_uid ' (' uid ') USING BTREE) Engine=innodb auto_increment=1334 DEF Ault Charset=utf8; CREATE TRIGGER ' Insert_before_trigger ' before insert on ' Replace_into ' for each ROW insert INTO ' trigger_log ' (' id ', ' table _name ', ' action ') VALUES (NULL, ' replace_into ', ' insert_before '); CREATE TRIGGER ' Insert_after_trigger ' after insert on ' Replace_into ' for each ROW insert INTO ' trigger_log ' (' id ', ' table_na Me ', ' action ') VALUES (NULL, ' replace_into ', ' insert_after '); CREATE TRIGGER ' Update_after_trigger ' after update on ' Replace_into ' for each ROW insert INTO ' trigger_log ' (' id ', ' table_na Me ', ' action ') VALUES (NULL, ' replace_into ', ' update_after '); CREATE TRIGGER ' Delete_after_trigger ' after delete on ' Replace_into ' for each ROW insert INTO ' trigger_log ' (' id ', ' table_na Me ', ' action ') VALUES (NULL, ' replace_into ', ' delete_after ');

After inserting some test data, execute replace into

[SQL] replace into ' replace_into ' values (1333,313, ' dd ') affected rows: 2 time: 0.036ms

You can see that the number of rows affected after execution succeeds is 2, let's take a look at the trigger's record

First step: Attempt to insert, but find uniqueness conflict, insert failed

Step two: Delete the old records for uniqueness conflicts in the table

Step three: Insert a new row

Summary: REPLACE into deletes the original row and then inserts a new line, which can cause poor performance if you write for lazy. Also, since the third step inserts a new row, the self-increment ID will also change if you do not specify a self-increment primary key.

INSERT ... On DUPLICATE KEY update starts after MySQL 4.1 release,
Execute statement:

[SQL] INSERT INTO ' replace_into ' values (null,313, ' aaaaaaaaaa ') on DUPLICATE KEY UPDATE ' name ' = ' ASD ' affected line: 2 time: 0.043MS

Look at the record of the trigger:

First step: Attempt to insert, but find uniqueness conflict, insert failed

Step two: Execute the UPDATE statement

Summary: Use the Insert ... On DUPLICATE KEY UPDATE, the original 3 SQL statements can be integrated into one, and will not cause delete old data.

MySQL replace into and insert ... On DUPLICATE KEY Update usage