【MySQL】replace into 浅析之一

最新推荐文章于 2024-05-10 10:52:49 发布
最新推荐文章于 2024-05-10 10:52:49 发布
阅读量183 收藏
点赞数
文章标签： 数据库
一 介绍
  在笔者支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结。从功能原理，性能和注意事项上做个说明。
二 原理
2.1 当表中存在主键但是不存在唯一建的时候。
表结构
  1. CREATE TABLE `yy` (
  2.   `id` bigint(20) NOT NULL,
  3.   `name` varchar(20) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. root@test 02:43:58>insert into yy values(1,'abc');
  2. Query OK, 1 row affected (0.00 sec)
  3. root@test 02:44:25>replace into yy values(2,'bbb');
  4. Query OK, 1 row affected (0.00 sec)
  5. root@test 02:55:42>select * from yy;
  6. +----+------+
  7. | id | name |
  8. +----+------+
  9. | 1 | abc |
  10. | 2 | bbb |
  11. +----+------+
  12. 2 rows in set (0.00 sec)
  13. root@test 02:55:56>replace into yy values(1,'ccc');
  14. Query OK, 2 rows affected (0.00 sec)
如果本来已经存在的主键值，那么MySQL做update操作。
  1. ### UPDATE test.yy
  2. ### WHERE
  3. ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
  4. ### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
  5. ### SET
  6. ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
  7. ### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
如果本来相应的主键值没有，那么做insert 操作  replace into yy values(2,'bbb');
### INSERT INTO test.yy
 ### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 623
#140314 2:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569
2.2 当表中主键和唯一键同时存在时
  1. CREATE TABLE `yy` (
  2.   `id` int(11) NOT NULL DEFAULT \'0\',
  3.   `b` int(11) DEFAULT NULL,
  4.   `c` int(11) DEFAULT NULL
  5.   PRIMARY KEY (`a`),
  6.   UNIQUE KEY `uk_bc` (`b`,`c`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
情形1 主键冲突
  1. root@test 04:37:18>replace into yy values(1,2,3);
  2. Query OK, 1 row affected (0.00 sec)
  3. root@test 04:37:37>replace into yy values(2,2,4);
  4. Query OK, 1 row affected (0.00 sec)
  5. root@test 04:38:05>select * from yy;
  6. +----+------+------+
  7. | id | b | c |
  8. +----+------+------+
  9. | 1 | 2 | 3 |
  10. | 2 | 2 | 4 |
  11. +----+------+------+
  12. 2 rows in set (0.00 sec)
  13. root@test 04:38:50>replace into yy values(1,2,5);
  14. Query OK, 2 rows affected (0.00 sec)
  15. root@test 04:38:58>select * from yy;
  16. +----+------+------+
  17. | id | b | c |
  18. +----+------+------+
  19. | 2 | 2 | 4 |
  20. | 1 | 2 | 5 |
  21. +----+------+------+
  22. 2 rows in set (0.00 sec)
主键冲突时，数据库对表做 先删除然后插入的操作，也即先删除id=1的记录，然后插入新的id=1 的记录(1,2,5).
  1. BINLOG '
  2. Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA
  4. ### DELETE FROM test.yy
  5. ### WHERE
  6. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  7. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  8. ### @3=3 /* INT meta=0 nullable=1 is_null=0 */
  9. Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA
  10. '/*!*/;
  11. ### INSERT INTO test.yy
  12. ### SET
  13. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  14. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  15. ### @3=5 /* INT meta=0 nullable=1 is_null=0 */
  16. # at 662
  17. #150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
  18. COMMIT/*!*/
情形2 唯一建冲突
  1. root@test 04:48:30>select * from yy;
  2. +----+------+------+
  3. | id | b | c |
  4. +----+------+------+
  5. | 1 | 2 | 4 |
  6. | 2 | 2 | 5 |
  7. | 3 | 3 | 5 |
  8. | 4 | 3 | 6 |
  9. +----+------+------+
  10. 4 rows in set (0.00 sec)
  11. root@test 04:53:21>replace into yy values(5,3,6);
  12. Query OK, 2 rows affected (0.00 sec)
  13. root@test 04:53:40>select * from yy;
  14. +----+------+------+
  15. | id | b | c |
  16. +----+------+------+
  17. | 1 | 2 | 4 |
  18. | 2 | 2 | 5 |
  19. | 3 | 3 | 5 |
  20. | 5 | 3 | 6 |
  21. +----+------+------+
  22. 4 rows in set (0.00 sec)
主键不冲突，唯一键冲突时，数据库对表 唯一键为(3，6)的行做update操作，将主键修改为要插入的值，id=4 改为id=5。
  1. BINLOG \'
  2. lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=
  4. \'/*!*/;
  5. ### UPDATE test.yy
  6. ### WHERE
  7. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
  8. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  9. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  10. ### SET
  11. ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
  12. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  13. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  14. # at 274
  15. #150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
  16. COMMIT/*!*/
情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。
  1. root@test 04:53:52>replace into yy values(1,3,6);
  2. Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3
  3. root@test 04:55:35>select * from yy;
  4. +----+------+------+
  5. | id | b | c |
  6. +----+------+------+
  7. | 2 | 2 | 5 |
  8. | 3 | 3 | 5 |
  9. | 1 | 3 | 6 |
  10. +----+------+------+
  11. 3 rows in set (0.00 sec)
  要插入的值(1,3,6) 主键于 表里面的id=1的值冲突，唯一键(3,6)和表中id=5的记录冲突，MySQL 处理的时候 ，先删除id=1的行，然后更新了id=5的行。
  1. BINLOG \'
  2. B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
  3. B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA
  4. ### DELETE FROM test.yy
  5. ### WHERE
  6. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  7. ### @2=2 /* INT meta=0 nullable=1 is_null=0 */
  8. ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
  9. B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=
  10. \'/*!*/;
  11. ### UPDATE test.yy
  12. ### WHERE
  13. ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
  14. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  15. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  16. ### SET
  17. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
  18. ### @2=3 /* INT meta=0 nullable=1 is_null=0 */
  19. ### @3=6 /* INT meta=0 nullable=1 is_null=0 */
  20. # at 510
  21. #150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
  22. COMMIT/*!*/
三 结论
   对表进行replace into操作的时候，
   当不存在冲突时,replace into 相当于insert操作。 
   当存在pk冲突的时候是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】
   当存在uk冲突的时候是直接update。 ,如果主键是自增的，则自增主键会做 +1 操作。   【5.5,5.6版本均做过测试】
   
了解上述原理和结论之后，以后再遇到replace into 的时候，相信各位读者可以知道如何选择，由于篇幅限制，后续 文章会基于replace into原理，讲述生产过程中的注意事项。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-1669734/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-1669734/

cojm55771
关注
replace into 浅析
weixin_34221036的博客
08-09 134
转载自：http://blog.itpub.net/22664653/viewspace-1669734/ http://blog.itpub.net/22664653/viewspace-1670120/ 一 介绍 在笔者支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结。从功能原理，性能和注意事项上做个说明。二 原理2.1 当表中存在主键但...
mysql replace的弊端_MySQL "replace into" 的坑
weixin_32067105的博客
01-16 932
MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE INTO。比如有这样一张表：CREATE TABLE`auto` (`id`int(10) unsigned NOT NULLAUTO_INCREMENT,`k`int(10) unsigned NOT NULL,`v`varchar(100) DEFAULT N...
mysql replace into 缺陷的解决方案
我的博客
04-16 424
replace into 实现原理是：如果新插入行的主键或唯一键在表中已经存在，则会删除原有记录并插入新行;如果在表中不存在，则直接插入。这样就会存在缺陷，如果某个字段有默认值，在使用replace into做数据修改时会将默认值的字段恢复到默认值，造成数据丢失。role_create_time与role_update_time并没有在修改条件中，但更新了。这样，在使用replace into 就会有隐患。id为2的两个时间并没有更新。的 merge into。做数据新增或修改，类似。
Replace into与Merge into的用法总结
夜空中最亮的星
10-11 6385
MySQL中replace用法总结 插入一条数据，存在则不操作，不存在就插入（必须现有唯一键） 使用insert ignore语句： insert ignore into table(col1,col2) values ('a','b'); 这样一来，如果表中已经存在的数据，就不会插入，如果没有就会插入一条新数据。 插入一条数据，存在就更新，不存在就插入（必须现有唯一键） 这种有两种操作...
MySQL---replace into 与replace到底有啥区别？-----抛砖引玉
Alien-Hu
03-02 1017
文章目录0.故事的背景1.replace into 与replace 使用方法2.replace into 与 replace 相同效果的场景3.replace into 与 replace 不同效果的场景1）.replace函数的具体情况2）.replace into 函数的具体情况 0.故事的背景 【表格结构】 CREATE TABLE `xtp_algo_white_list` ( `strategy_type` int DEFAULT NULL, `user_name` varchar(
MySQL replace into 语句浅析（一）
09-10
MySQL中的`REPLACE INTO`语句是一个非常实用的数据库操作，它在特定场景下能替代`INSERT`和`UPDATE`的组合使用。本篇文章将深入浅出地解析`REPLACE INTO`的原理、使用方法以及适用场景，并通过实例来演示其工作方式...
浅析MySQL replace into 的用法
01-19
在 SQL Server 中可以这样处理： 代码如下: if not exists (select 1 from t where id = 1) ...MySQL 中有更简单的方法： replace into 代码如下: replace into t(id, update_time) values(1, now());或 repl
MySQL replace into 语句浅析（二）
01-19
本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的情况发生。 二 案例分析 在主库上操作 代码如下: root@test 12:36...
mysql批量更新方法
yuanmeifang的博客
09-23 4662
mysql批量更新方法
MySQL 几种DML方式：ignore、replace into、DUPLICATE KEY UPDATE、类似merge into
关注系统性能调优
05-02 726
mysql> select version(); +------------+ | version()  | +------------+ | 5.6.24-log | +------------+ DROP TABLE t; CREATE TABLE t (   id INT NOT NULL PRIMARY KEY,   id1 INT AUTO_INCREMENT,
SpringBoot中六种批量更新Mysql 方式效率对比
最新发布
Java领域优秀创作者
05-10 2166
sql语句for循环效率其实相当高的，因为它仅仅有一个循环体，只不过最后update语句比较多，量大了就有可能造成sql阻塞,同时在mysql的url上需要加上allowMultiQueries=true参数,即 jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true(公司项目不一定加,我们也不一定有权限加)。
mysql中replace into效率_MYSQL中replace into的用法
weixin_30595763的博客
01-19 2488
做项目是遇到这样一个问题，把查询出的数据插入到一个新表里面，第一次可以直接插入，但是第二次第三次的时候如果直接更新，但是会有些新的数据需要添加，但是如果先删除再插入的话效率不高，如果对比两端的数据，相同的更新，没有的插入，这杨效率也不高，就是有些数据是更新,有些数据是新增,在网上找了找，mysql有一个replace into可以实现，有新数据就自己新增，旧数据就直接更新,其实就是先删除数据，再新...
从20s优化到500ms，我用了这三招
lisu061714112的专栏
07-01 2120
接口性能问题，对于从事后端开发的同学来说，是一个绕不开的话题。想要优化一个接口的性能，需要从多个方面着手。其实，我之前也写过一篇接口性能优化相关的文章《聊聊接口性能优化的11个小技巧》，发表之后在全网广受好评，感兴趣的小伙们可以仔细看看。本文将会接着接口性能优化这个话题，从实战的角度出发，聊聊我是如何优化一个慢查询接口的。上周我优化了一下线上的批量评分查询接口，将接口性能从最初的，优化到目前的以内。总体来说，用三招就搞定了。到底经历了什么？我们每天早上上班前，都会收到一封线上慢查询接口汇总邮件，邮件中会展示
MySQL replace into行为解析
yhj198927的博客
05-25 1905
1、不冲突，replace执行语句为insert。2、只存在主键或只存在唯一索引冲突，replace执行update。3、同时存在主键和唯一索引，唯一索引冲突，replace执行update。4、同时存在主键和唯一索引，主键冲突，replace先delete再insert。5、同时存在主键和唯一索引冲突，同一行数据，replace先delete再insert。6、同时存在主键和唯一索引冲突，不同一行数据，replace先delete再update。
记一次replace into引发的死锁问题
weiweiracings的博客
11-23 4350
记一次replace into引发的死锁问题 功能要求是使用kafka监听历史表的变动，写入到新表之中。代码如下 A code block var foo = 'bar';
MySQL replace into详解
weixin_30657541的博客
07-21 287
replace into的存在的几种情况 当表存在主键并且存在唯一键的时候 如果只是主键冲突 mysql> select * from auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | ex...
insert大量数据经验之谈
丢失青春的岁月
10-31 1522
[size=large][align=center]insert大量数据经验之谈[/align][/size] 　　在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据insert的一些经验。 　　前提：在做insert数据之前，如果是非生产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。 　　...
【MySQL】replace into 浅析之二
cojm55771的博客
05-25 205
一 介绍 上一篇文章介绍了replace into的基本原理。本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不...
MySQL replace into语句详解：原理、用法与实战
MySQL `replace into` 语句浅析（一） 在数据库管理中，`replace into` 是一个强大的工具，特别是在处理MySQL中的数据更新与插入操作时。本文将深入探讨replace into的原理、使用方法以及适用场景，以帮助开发者更...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值