"Go" MySQL replace into with INSERT into on duplicate key update real difference

Original link: http://www.jb51.net/article/47090.htm Today listen to colleagues about Oracle to MySQL data migration, he used insert into ... on duplicate key Update ..., I was thinking how not to replace it, so I came back to look carefully, they really still have a different look at the following example:
1 Replace into ... 1.1 Input raw Data mysql> use test; Database changed mysql>

mysql> CREATE TABLE T1 SELECT 1 as a, ' C3 ' as B, ' C2 ' as C; ALTER TABLE T1 Change a a INT PRIMARY KEY auto_increment; Query OK, 1 row affected (0.03 sec) records:1 duplicates:0 warnings:0

mysql> INSERT into T1 SELECT 2, ' 2 ', ' 3 '; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0 mysql> insert into T1 (b,c) Select ' R2 ', ' R3 '; Query OK, 1 row affected (0.08 sec) records:1 duplicates:0 warnings:0

1.2 Start replace operation mysql> replace into T1 (A, B) VALUES (2, ' a '); Query OK, 2 rows affected (0.06 sec)

"" See here, replace, see here, a=2 's record in the C field is empty string, so when the conflict with key, replace overwrite related fields, other fields fill the default values, can be understood to delete duplicate key record, new insert a record, An operation that deletes the original record and then inserts .

1.3 But do not know the auto_increment of the primary key has no effect, next test:

mysql> INSERT INTO T1 (b,c) Select ' R4 ', ' R5 '; Query OK, 1 row affected (0.05 sec) records:1 duplicates:0 warnings:0 mysql> select * from T1; +---+----+----+ | A | B | C | +---+----+----+ | 1 | C3 | C2 | | 2 | A | | | 3 | R2 | R3 | | 5 | R4 | R5 | +---+----+----+ 4 rows in Set (0.00 sec)

As can be seen from here, the new increment is not starting from 4, but starting from 5, representing a repalce operation, and the auto_increment in the primary key will accumulate 1. So the summary is as follows: Replace:

When there is no key, replace is equivalent to a normal insert. When there is a key, it can be understood to delete the duplicate key record, in the case of keeping the key unchanged, delete the original record, and then insert a new record, the record value will only enter the value of the field in the Replace statement, the rest of the field in the Replace statement, the default value will be automatically populated.

2.1 OK, and see INSERT INTO ... on duplicate key update,

mysql> INSERT INTO T1 (A, b) select ' 3 ', ' R5 ' on duplicate key update b= ' R5 '; Query OK, 2 rows affected, 1 Warning (0.19 sec) records:1 duplicates:1 warnings:1 mysql> select * from T1; +---+----+----+ | A | B | C | +---+----+----+ | 1 | C3 | C2 | | 2 | A | | | 3 | R5 | R3 | | 5 | R4 | R5 | +---+----+----+ 4 rows in Set (0.00 sec)

"" At a=5 time, the original C value is still there, which means that when key is sometimes executed, only the following udate action statements are performed.

2.2 Check again the auto_increment condition.

As you can see from here, the new increment is not starting from 6, but starting from 7, which represents an insert. On Deplicate udate operation, the auto_increment in the primary key also accumulates as replace 1.

2.3 Look again when there is no key, insert. On deplicate update condition

mysql> INSERT INTO T1 (a,b,c) Select ' A ', ' R5 ', ' C3 ' on duplicate key update b= ' R5 '; Query OK, 1 row affected, 1 warning (0.23 sec) records:1 duplicates:0 warnings:1 mysql> select * from T1; +----+----+----+ | A | B | C | +----+----+----+ | 1 | C3 | C2 | | 2 | A | | | 3 | B5 | R3 | | 5 | R4 | R5 | | 7 | R6 | R7 | | 9 | S6 | S7 | | 33 | R5 | C3 | +----+----+----+ 7 rows in Set (0.00 sec)

See A=33 Records, OK, all entered.

3 Summary from the above test results see, the same: (1), when there is no key, replace and insert. On Deplicate udpate is the same. (2), when there is a key, all retain the primary key value, and Auto_increment automatic +1 different: when there is a key, replace is the delete old record, and enter a new record, so the original records will be cleared, this time,       If the field of the Replace statement is not complete, some of the original values of the C field, such as the example, are automatically populated with default values. and insert:      Deplicate update only executes the SQL after the update tag, which is equivalent to a simple UPDATE statement on the façade. But in fact, according to my speculation, if it is a simple UPDATE statement, auto_increment not +1, should also be the first delete, then insert operation, Only the values of all fields except the field after the update are preserved during insert.
So the difference between the two is only one,insert: On Deplicate udpate retains the old values of all the fields, overwrites and then inserts them together, and replace does not retain the old values, delete them and insert the new values directly. from the bottom-level execution efficiency, replace is more than insert. On deplicate update is efficient, but when writing replace, the fields are written in full, preventing old field data from being deleted.

"Go" MySQL replace into with INSERT into on duplicate key update real difference