Sogou Replace into MySQL Insert into on update duplicate key... The real difference

14445 people read comment(5) Collection Report
Classification:


Today listen to colleagues Oracle to MySQL data migration, he used the insert into... On duplicate key update.... I thought why not replace, then come back to carefully check the next, they really are different, look at the following example:


1 into Replace...
1.1 input raw data

Use test mysql>;
Changed Database
Mysql>

CREATE TABLE T1 SELECT AS 1 a'c3', AS B'c2', AS C mysql>;
TABLE T1 CHANGE a ALTER a INT PRIMARY KEY AUTO_INCREMENT;
OK Query, 1 affected row (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0

 

INSERT INTO T1 SELECT mysql> 2,'2','3';
OK Query, 1 affected row (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Insert into T1 mysql> (B, c)'r2'select,'r3';
OK Query, 1 affected row (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0

 

1.2 start replace operation
REPLACE INTO T1 a (, b) VALUES (2,'a'), mysql> ();
OK Query, 2 affected rows (0.06 sec)

[] replace, see here, see here, records of the a=2 C field is empty,
So when the conflict with replace, key cover related fields, other fields to fill the default value, you can understand to delete duplicate key records, the new insert a record, a delete original records and then insert operation.

 

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

Insert into T1 mysql> (B, c)'r4'select,'r5';
OK Query, 1 affected row (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

Select mysql> * T1 from;
+---+----+----+
A B C
+---+----+----+
1 C3 C2
2 A
3 R2 R3
5 R4 R5
+---+----+----+
4 in set rows (0 sec)

 

From here we can see that the new self growth is not from the beginning of 4, but from the beginning of 5, said a repalce operation, the primary key in the auto_increment will accumulate 1
So the summary is as follows:
Replace:

When there is no replace, key is equivalent to an ordinary insert.
When there is a key, can be understood to delete duplicate key records, under the same key, delete the original records, and then insert a new record, record values replace statement entry field values, remained in the replace statement in the field, will automatically filled with default values.

 

2.1 OK, come back to see into Insert... Duplicate key update on,

Insert into T1 mysql> (a, b)'3'select, on duplicate key'r5' update b='r5';
OK Query, 2 affected rows, 1 warning (0.19 sec)
Records: 1 Duplicates: 1 Warnings: 1

Select mysql> * T1 from;
+---+----+----+
A B C
+---+----+----+
1 C3 C2
2 A
3 R5 R3
5 R4 R5
+---+----+----+
4 in set rows (0 sec)

 

[] a=5, the original C value is still in, which means that when key sometimes, only the implementation of the back of the udate operation statement.

 

2.2 check the auto_increment case again.

Insert into T1 mysql> (a, b)'3'select, on duplicate key'r5' update b='r5';
OK Query, 2 affected rows, 1 warning (0.19 sec)
Records: 1 Duplicates: 1 Warnings: 1

Select mysql> * T1 from;
+---+----+----+
A B C
+---+----+----+
1 C3 C2
2 A
3 R5 R3
5 R4 R5
+---+----+----+
4 in set rows (0 sec)

Insert into T1 mysql> (B, c)'r6'select,'r7';
OK Query, 1 affected row (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0

Select mysql> * T1 from;
+---+----+----+
A B C
+---+----+----+
1 C3 C2
2 A
3 R5 R3
5 R4 R5
7 R6 R7
+---+----+----+
5 in set rows (0 sec)


From here we can see that the new self growth is not from the beginning of 7, but from the beginning of 6, it means that a on.. deplicate udate Insert operation, the primary key in the auto_increment also with replace as accumulated 1.

 

2.3再看下当没有关键的时候,插入。在deplicate更新的情况

MySQL >插入T1(A,B,C)选择“33”,'r5 ','c3 '重复密钥更新B = 'r5”;
查询确定,1行受影响,1警告(0.23秒)
记录:1重复:0警告:1

MySQL SELECT * FROM T1 >;
+ ---- + ---- +
|一| B | C |
+ ---- + ---- +
| 1 | | | C2 C3
| 2 |一| |
| 3 | B5 | R3 |
| 5 | R4 | R5 |
| 7 | R6 R7 | |
| 9 | S6 | S7 |
| 33 | R5 | C3 |
+ ---- + ---- +
7行(0秒)

看= 33的记录,OK,全部录入了。

 

3总结从上面的测试结果看出,相同之处:
(1)、没有关键的时候,取代与插入。在deplicate更新相同。
(2)、有关键的时候,都保留主键值,并且auto_increment自动+ 1
不同之处:有关键的时候,取代是删除老记录,而录入新的记录,所以原有的所有记录会被清除,这个时候,如果取代语句的字段不全的话,有些原有的比如例子中C字段的值会被自动填充为默认值。
而插入。deplicate更新则只执行更新标记之后的SQL,从表象上来看相当于一个简单的更新语句。
      但是实际上,根据我推测,如果是简单的更新语句,auto_increment不会+ 1,应该也是先删除,再插入的操作,只是在插入的过程中保留除更新后面字段以外的所有字段的值。
  
所以两者的区别只有一个,插入..在deplicate更新保留了所有字段的旧值,再覆盖然后一起插入进去,而取代没有保留旧值,直接删除再插入新值。
从底层执行效率上来讲,取代要比插入。在deplicate更新效率要高,但是在写取代的时候,字段要写全,防止老的字段数据被删除。

 

个人倾向与用取代。

 

二十三
猜你在找
查看评论
*以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1015788次
    • 积分:一万六千二百三十七
    • 等级:
    • 排名:316名第
    • 原创:439篇
    • 转载:3篇
    • 译文:2篇
    • 评论:525条
    我的课程
    博客专栏
    技术网址连接
    http://dev.mysql.com/doc/
    文章分类
    文章存档
    Latest comments