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

时间:2022-09-21 16:51:00
原文链接: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的Replace into 与Insert into on duplicate key update真正的不同之处

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的Replace into 与Insert into on duplicate key update真正的不同之处

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的Replace into 与Insert into on duplicate key update真正的不同之处

1.2 开始replace操作 mysql> REPLACE INTO t1(a,b) VALUES(2,'a') ; Query OK, 2 rows affected (0.06 sec)

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

【】看到这里,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的时候,字段要写全,防止老的字段数据被删除。

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

  1. mysql 中 replace into 与 insert into on duplicate key update 的使用和不同点

    replace into和insert into on duplicate key update都是为了解决我们平时的一个问题 就是如果数据库中存在了该条记录,就更新记录中的数据,没有,则添加记录. ...

  2. Replace into 与Insert into on duplicate key update的区别

    前提条件:除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用这2条语句没有意义.该语句会与INSERT相同 1. Replace into (1)   添加相同的主键 操作前       ...

  3. MySql中4种批量更新的方法update table2,table1,批量更新用insert into ...on duplicate key update, 慎用replace into.

    mysql 批量更新记录 MySql中4种批量更新的方法最近在完成MySql项目集成的情况下,需要增加批量更新的功能,根据网上的资料整理了一下,很好用,都测试过,可以直接使用. mysql 批量更新共 ...

  4. insert into on duplicate key update

    问题 有一个表,建表语句如下: CREATE TABLE `tbl_host` ( `id` bigint(64) NOT NULL AUTO_INCREMENT, `ip` varchar(255) ...

  5. SQL语句实现不存在即插入,存在则increase某字段的功能insert into … on duplicate key update

    前提条件:必须是唯一主键: CREATE UNIQUE INDEX idx_vote_object ON test_customers_vote (`vote_object`, `vote_objec ...

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

    on duplicate key update:针对主健与唯一健,当插入值中的主健值与表中的主健值,若相同的主健值,就更新on duplicate key update 后面的指定的字段值,若没有相同 ...

  7. (转载)[MySQL技巧]INSERT INTO… ON DUPLICATE KEY UPDATE

    (转载)http://blog.zol.com.cn/2299/article_2298921.html MySQL 自4.1版以后开始支持INSERT … ON DUPLICATE KEY UPDA ...

  8. INSERT 中ON DUPLICATE KEY UPDATE的使用

    如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE.例如,如果列a被定义为UNIQUE,并 ...

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

    有时候需要进行数据操作的,如果有数据则更新数据, 没有数据则插入. 以往的做法是先查询,再根据查询结果进行判断,执行插入或更新操作 其实 有一种 ON DUPLICATE KEY UPDATE 语法, ...

随机推荐

  1. Android 5.X新特性之RecyclerView基本解析及无限复用

    说到RecyclerView,相信大家都不陌生,它是我们经典级ListView的升级版,升级后的RecyclerView展现了极大的灵活性.同时内部直接封装了ViewHolder,不用我们自己定义Vi ...

  2. [转] nodemon 基本配置与使用

    在开发环境下,往往需要一个工具来自动重启项目工程,之前接触过 python 的 supervisor,现在写 node 的时候发现 supervisior 在很多地方都有他的身影,node 也有一个 ...

  3. [SAP ABAP开发技术总结]预定义(内置)数据类型

    声明:原创作品,转载时请注明文章来自SAP师太技术博客( 博/客/园www.cnblogs.com):www.cnblogs.com/jiangzhengjun,并以超链接形式标明文章原始出处,否则将 ...

  4. 解决:Android4.3锁屏界面Emergency calls only - China Unicom与EMERGENCY CALL语义重复

    从图片中我们可以看到,这里在语义上有一定的重复,当然这是谷歌的原始设计.这个问题在博客上进行共享从表面上来看着实没有什么太大的意义,不过由于Android4.3在锁屏功能上比起老版本做了很大的改动,而 ...

  5. OC - 12.NSURLRequest与NSURLConnection

    ##NSURLRequest NSURLRequest封装了一次网络请求所需要的数据,主要封装了以下信息: 请求路径(URL) 请求方法(GET或POST) 请求头 请求体 超时参数 NSURLReq ...

  6. Dom4j之xPath

    XPath 是一门在 XML 文档中查找信息的语言.XPath 可用来在 XML 文档中对元素和属性进行遍历. XPath 是 W3C XSLT 标准的主要元素,并且 XQuery 和 XPointe ...

  7. VS2005 添加onTimer定时器

    SetTimer(1,300,NULL); void CchangeDisplayDlg::OnTimer(UINT_PTR nIDEvent) { // TODO: 在此添加消息处理程序代码和/或调 ...

  8. hdu 5489(LIS最长上升子序列)

    题意:一个含有n个元素的数组,删去k个连续数后,最长上升子序列        /*思路参考GoZy 思路: 4 2 3 [5 7 8] 9 11 ,括号表示要删掉的数, 所以  最长上升子序列  = ...

  9. mysql 关联表修改数据

    UPDATE t1 INNER JOIN t2 ON t1.c1=t2.c1  SET t1.c2=value WHERE t1`removed`=0 AND t2`removed`=0 AND t1 ...

  10. 如何快速上手一个新技术之vue学习经验

    碰到紧急项目挪别人的vue项目过来直接改,但是vue是18年初看过一遍,18年底再来用,早就忘到九霄云外了,结果丢脸的从打开vue开始学,虽然之前在有道云笔记做了很多记录,然后没有系统整理.所以借这次 ...