【转】MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

原文链接:http://www.jb51.net/article/47090.htm

今天听同事介绍oracle到mysql的数据migration,他用了Insert into ..... on duplicate key update ...,我当时就想怎么不用Replace呢,于是回来就仔细查了下,它们果然还是有区别的

看下面的例子吧: 
1 Replace into ... 1.1 录入原始数据 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 开始replace操作 mysql> REPLACE INTO t1(a,b) VALUES(2,‘a‘) ; Query OK, 2 rows affected (0.06 sec)

【】看到这里,replace,看到这里,a=2的记录中c字段是空串了, 所以当与key冲突时,replace覆盖相关字段,其它字段填充默认值,可以理解为删除重复key的记录,新插入一条记录,一个delete原有记录再insert的操作

1.3 但是不知道对主键的auto_increment有无影响,接下来测试一下:

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)

【】从这里可以看出,新的自增不是从4开始,而是从5开始,就表示一个repalce操作,主键中的auto_increment会累加1. 所以总结如下: Replace:

当没有key时,replace相当于普通的insert. 当有key时,可以理解为删除重复key的记录,在保持key不变的情况下,delete原有记录,再insert新的记录,新纪录的值只会录入replace语句中字段的值,其余没有在replace语句中的字段,会自动填充默认值。

2.1 ok,再来看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)

【】a=5时候,原来的c值还在,这表示当key有时,只执行后面的udate操作语句.

2.2 再检查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)
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) 

【】从这里可以看出,新的自增不是从6开始,而是从7开始,就表示一个Insert .. on deplicate udate操作,主键中的auto_increment也跟replace一样累加1.

2.3 再看下当没有key的时候,insert .. on deplicate update的情况

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)

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

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

时间: 05-24

【转】MySQL的Replace into 与Insert into on duplicate key update真正的不同之处的相关文章

INSERT 中ON DUPLICATE KEY UPDATE的使用

使用场景,在做全国各省ip访问统计时要将sparkStreaming的数据存在mysql中,按照一般设计,id,province,counts,time,这样就需要每次清空表,但是如果多分区的话就存在删除表的时候回出现后一个分区可能把前一个分区的数据删除掉,当然最好的办法是每次都只更新而不删除,但是如果通过代码实现比较复杂. 此时我们需要的就是如果存在则更新,如果不存在则新增. 用redis的kv就可以很容易的实现.在MySQL中也有这样的功能.INSERT 中ON DUPLICATE KEY

insert into ... on duplicate key update 与 replace 区别

on duplicate key update:针对主健与唯一健,当插入值中的主健值与表中的主健值,若相同的主健值,就更新on duplicate key update 后面的指定的字段值,若没有相同主健值,就插入该记录 mysql> create table tab_test(fd1 int NOT NULL,fd2 varchar(50),primary key(fd1))engine=innodb; Query OK, 0 rows affected (0.22 sec) mysql>

INSERT into ... on duplicate key update ...

INSERT  into exception_report_total ( waitFillOrders ,waitSendOrders ,changeOrders ,changeTotal ,waitProduceOrders ,wmsStockOrders ,erpStockOrders ,waitReserveOrders ,waitDispatchOrders ,addDispatchOrders ,waitCancel ,sendExceptions ,reserveOrders ,c

mysql 插入更新在一条sql ON DUPLICATE KEY UPDATE

有时候需要进行数据操作的,如果有数据则更新数据, 没有数据则插入. 以往的做法是先查询,再根据查询结果进行判断,执行插入或更新操作 其实 有一种 ON DUPLICATE KEY UPDATE 语法, 可以在一条sql里完成上述逻辑操作. INSERT INTO tbPrize(sOpenid,iAreaId,iPlatId,SSSgroupOne,SSSgroupTwo,SSgroupOne,SSgroupTwo,SgroupOne,SgroupTwo,dtFound) VALUES ('2A

mysql ON DUPLICATE KEY UPDATE、REPLACE INTO

INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO,两个命令可以处理重复键值问题,在实际上它之间有什么区别呢?前提条件是这个表必须有一个唯一索引或主键. 1.REPLACE发现重复的先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空.2.INSERT发现重复的是更新操作.在原有记录基础上,更新指定字段内容,其它字段内容保留. 这样REPLACE的操作成本要大于 insert  ON DUPLICATE

mysql ON DUPLICATE KEY UPDATE重复插入时更新

mysql当插入重复时更新的方法: 第一种方法: 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: INSERT INTO clients (client_id,client_name,client_type) SELECT supplier_id,supplier_name,'advertising' FROM suppliers WHERE not exists(select * from clients where clients.c

INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the f

解决MySQL复合主键下ON DUPLICATE KEY UPDATE语句失效问题

先描述一下这个问题的起因,假设有一张表,里面保存了交易订单,每张订单有唯一的ID,有最后更新时间,还有数据,详情如下: +-------+----------+------+-----+---------------------+-------+ | Field | Type     | Null | Key | Default             | Extra | +-------+----------+------+-----+---------------------+-------

MySQL使用on duplicate key update时导致主键不连续自增

使用on duplicate key update语法有时是很方便,但是会有一个影响:默认情况下,每次更新都会更新该表的自增主键ID,如果更新频率很快,会导致主键ID自增的很快,过段时间就超过数字类型的的范围了解决这个问题,有两种方式:(实际目前的方式就是把自增主键ID设置为bigint,也有一部分操作先查询再选择插入OR更新)方法一:拆分成两个动作,先查询,再更新方法二:修改innodb_autoinc_lock_mode参数(不推荐)  innodb_autoinc_lock_mode中有3