Replace into and Insert into... on duplicate key update... true

Replace into and Insert... on duplicate key update... what's really different 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: www.2cto.com 1 Replace... 1.1 enter the original data mysql> use test; Database changedmysql> mysql> show tables; www.2cto.com + ---------------- + | Tables_in_test | + ---------------- + | test | + ---------------- + 1 ro W 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 se C) 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 replace mysql> replace into t1 (a, B) VALUES (2, 'A'); Query OK, 2 rows affected (0.06 sec) mys Ql> 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 an empty string. Therefore, when it is in conflict with the key, replace overwrites the related field, and other fields are filled with the default value, it can be understood that the operation is to delete records 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. Next, let's 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) [] 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: when no If there is a key, 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 ..... 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 subsequent udate operation statement. 2.2 then checks auto_increment. 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) Rec Ords: 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. When no key exists, insert .. on deplicate update mysql> insert into t1 (a, B, c) select '33', 'r 5', '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. Conclusion: (1) replace is the same as insert .. on deplicate udpate if no key exists. (2) When a key exists, all the primary key values are retained, and auto_increment automatically + 1 is different: when a key exists, replace is the old delete record, and a new record is input, therefore, all the original records will be cleared. In this case, if the replace statement fields are incomplete, some original values of field c in this example will be automatically filled with the default values. 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.