Replaceinto and Insertinto... onduplicatekeyupdate... _ MySQL

Replaceinto and Insertinto... onduplicatekeyupdate... true difference bitsCN.com

The real difference between Replace into and Insert into... on duplicate key update... in MySQL

Today, I heard from my colleagues about the data migration from oracle to mysql. he used Insert ..... on duplicate key update ..., I was wondering why I didn't need Replace at the time, so I checked them back and found out the difference. let's look at the example below:

1 Replace...

1.1 enter raw data

Mysql> use test;

Database changed

Mysql>

Mysql>

Mysql> show tables; www.bitsCN.com

+ ---------------- +

| Tables_in_test |

+ ---------------- +

| Test |

+ ---------------- +

1 row in set (0.00 sec)

Mysql> create table t1 SELECT 1 AS a, 'C3' AS B, 'C2' AS c;

Alter table t1 CHANGE a int primary key AUTO_INCREMENT;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql> SELECT * FROM t1;

+ --- + ---- +

| A | B | c |

+ --- + ---- +

| 1 | c3 | c2 |

+ --- + ---- +

1 row in set (0.00 sec)

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

Mysql> select * from t1;

+ --- + ---- +

| A | B | c |

+ --- + ---- +

| 1 | c3 | c2 |

| 2 | 2 | 3 |

| 3 | r2 | r3 |

+ --- + ---- +

3 rows in set (0.00 sec)

1.2 Start the replace operation

Mysql> replace into t1 (a, B) VALUES (2, 'A ');

Query OK, 2 rows affected (0.06 sec)

Mysql> select * from t1;

+ --- + ---- +

| A | B | c |

+ --- + ---- +

| 1 | c3 | c2 |

| 2 | a |

| 3 | r2 | r3 |

+ --- + ---- +

3 rows in set (0.00 sec)

[] Here, replace. here, the c field in the = 2 record is empty,

Therefore, in case of a key conflict, replace overwrites the relevant fields. if other fields are filled with the default values, you can delete the record with duplicate keys, insert a new record, and delete the original record before insert.

1.3 but do not know whether the auto_increment of the primary key has any impact. let's test it:

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)

[] From this we can see that the new auto-increment is not from 4, but from 5, it indicates a repalce operation, and the auto_increment in the primary key will accumulate 1.

So the summary is as follows:

Replace:

If no key exists, replace is equivalent to a normal insert.

When there is a key, it can be understood as deleting records with duplicate keys. when the key remains unchanged, the original record is deleted and a new record is inserted, the value of the new record is only the value of the field in the replace statement. other fields that are not in the replace statement are automatically filled with the default value.

2.1 OK, let's 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)

[] When a = 5, the original c value is still there, which means that when the key is sometimes, only the following udate operation statement is executed.

2.2 check the auto_increment status again.

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)

Mysql> insert into t1 (B, c) select 'r6', 'r7 ';

Query OK, 1 row affected (0.19 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql> select * from t1;

+ --- + ---- +

| A | B | c |

+ --- + ---- +

| 1 | c3 | c2 |

| 2 | a |

| 3 | r5 | r3 |

| 5 | r4 | r5 |

| 7 | r6 | r7 |

+ --- + ---- +

5 rows in set (0.00 sec)

[] From here we can see that the new auto-increment is not from 6, but from 7, it indicates an Insert .. on deplicate udate operation, the auto_increment in the primary key also accumulates 1 like replace.

2.3 check the insert .. on deplicate update statement when no key exists.

Mysql> insert into t1 (a, B, c) select '33', '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)

Check the records of a = 33. OK. All records are entered.

3. the test results show the similarities:

(1) if no key exists, replace is the same as insert .. on deplicate udpate.

(2) when there is a key, all the primary key values are retained, and auto_increment automatically + 1

Difference: when a key exists, replace is the old delete record, and a new record is input, so all the original records will be cleared. in this case, if the replace statement fields are incomplete, in some examples, the c field value is automatically filled with the default value.

Insert... deplicate update only executes the SQL statement after the update mark, which is equivalent to a simple update statement.

But in fact, according to my estimation, if it is a simple update statement, auto_increment will not + 1, it should also be the first delete and then insert operation, only the values of all fields except the fields after update are retained during the insert process.

So there is only one difference between the two, insert .. on deplicate udpate retains the old values of all fields, overwrites them, and inserts them together. replace does not retain the old values, and then directly deletes and inserts the new values.

In terms of the underlying execution efficiency, replace is more efficient than insert .. on deplicate update. However, when writing replace, the fields must be fully written to prevent the old field data from being deleted.

Personal disposition and use of Replace.

BitsCN.com