【MySQL】浅析“replace into 操作” 与 “insert into+ON DUPLICATE KEY UPDATE操作”

最新推荐文章于 2024-06-11 17:26:03 发布
最新推荐文章于 2024-06-11 17:26:03 发布
阅读量990 收藏 2
点赞数 1
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yangchunlu0101/article/details/80249896
版权

一 介绍

  在笔者支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结。从功能原理，性能和注意事项上做个说明。

同时说明下insert into+ON DUPLICATE KEY UPDATE操作的使用区别和场景。

二 原理

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操作。

如果本来已经存在的主键值，那么MySQL做delete+insert操作。

  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');

  1. ### INSERT INTO test.yy
  2. ### SET
  3. ### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
  4. ### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
  5. # at 623
  6. #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。

主键不冲突，唯一键冲突时，数据库对表 先删除 唯一键为(3，6)的行，再做Insert操作。

  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/*!*/

2.3 Insert into + ON Duplicate key update操作

对有主键和唯一键的表进行insert into+ON DUPLICATE KEY UPDATE操作
(product)root@localhost [test]> CREATE TABLE `student` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `sid` varchar(6) NOT NULL,
    ->   `name` varchar(10) NOT NULL DEFAULT '',
    ->   `class` varchar(10) NOT NULL DEFAULT '',
    ->   `score` int(3) NOT NULL DEFAULT '0',
    ->  primary key(`id`),
    ->  unique (`sid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]>  select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> insert into student (sid,name,class,score) value('0001','f','3',66) ON DUPLICATE KEY UPDATE name=values(name),class=values(class),score=values(score);
Query OK, 2 rows affected (0.00 sec)


(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | f    | 3     |    66 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

三 结论

对表进行replace into操作的时候，

当不存在冲突时,replace into 相当于insert操作。 

当存在pk冲突的时候是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】

当存在uk冲突的时候是直接update。,如果主键是自增的，则自增主键会做 +1 操作。   【5.5,5.6版本均做过测试】

当存在pk或者uk冲突的时候都是先delete再insert,如果主键是自增的，则自增主键会做 +1 操作。【5.5,5.6版本均做过测试】


   

了解上述原理和结论之后，以后再遇到replace into 的时候，相信各位读者可以知道如何选择，由于篇幅限制，后续文章会基于replace into原理，讲述生产过程中的注意事项。


四 使用replace into的负面影响

所以在对有自增主键和唯一键存在的表进行replace into操作时，会有如下问题：

1.新记录与老记录的主键值不同，所以其他表中所有与本表老数据主键id建立的关联全部会被破坏。

2.频繁的replace into会造成新记录的主键的值迅速增大。当达到最大值后就会因为数据太大溢出，而没法再插入新记录。

3.主库备库切换后，如果使用replace into 会导致数据影响。

4.如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为replace into 操作可能会导致主键的重新组织.

参考链接：

    【MySQL】replace into 浅析之二 http://blog.itpub.net/22664653/viewspace-1670120/

            测试各场景下replace into的用法 https://blog.csdn.net/zengxuewen2045/article/details/52354958

            

lcyGo
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql replace的弊端_mySQL中replace的用法
weixin_39821330的博客
01-16 681
MySQL replace函数我们经常用到，下面就为您详细介绍MySQL replace函数的用法，希望对您学习MySQL replace函数方面能有所启迪mysql replace实例说明：UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');REPLACE(str,from_str,to_str)在字符串 str 中所有出现的字符串 from_str 均被 t...
mysql中的insert ON DUPLICATE KEY UPDATE，replace语句
敖尔其楞的专栏
04-22 467
之前没有关注过的一个点，在insert ON DUPLICATE KEY UPDATE执行后，表上的自增主键值维护是加了1的，主键一样，执行的就是update，不是删除在插入 mysql> select * from bai; +------+------+------+----+ | a | b | c | id | +------+------+------+----+...
mysql ON DUPLICATE KEY UPDATE语句示例
09-10
本文介绍一下关于mysql中INSERT INTO… ON DUPLICATE KEY UPDATE用法
MySQL：replace函数的几种实用场景
陈哈哈的菜园子
09-06 1万+
MySQL的Replace函数都有哪些用法，你是否都知晓呢？今天，让我带大家花几分钟时间来一起看一看，记得Mark！ 目录REPLACE语法操作实例使用场景1 -- (Query语句)：使用场景2 -- (update语句)：使用场景3 -- (插入或替换：REPLACE INTO)附、一张有故事的照片（五） REPLACE语法 REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str，这里的from_str不支持正则匹配。 操作实.
ON DUPLICATE KEY UPDATE 子句和 REPLACE INTO 语句
最新发布
Eucalyptus's blog
06-11 267
整个记录会被替换，不仅仅是冲突的字段，这意味着如果新记录没有指定某些字段的值，这些字段将使用默认值或NULL（如果没有默认值）。在尝试插入新记录时，如果发现主键或唯一索引冲突，它会先删除原有的冲突行，然后插入新的记录。：它允许你指定哪些列需要在冲突发生时进行更新，以及如何更新这些列的值。：适用于需要确保每个唯一键对应的记录完全替换的场景，例如，当需要确保数据的绝对新鲜性，不关心被替换记录的其他字段值时。可能会消耗更多的资源，因为它涉及到删除旧记录和插入新记录两个操作，特别是在处理大表时可能会影响性能。
[Mysql] REPLACE函数
Hudas的博客
03-26 1万+
本文主要介绍REPLACE函数的用法
MySQL REPLACE()字符串函数
编程火箭车（Coding Rocket）专注编程领域
02-05 6897
由于数据库中某些数据需要批量替换纠正错误，就使用到了REPLACE()函数，在这里小记一下，供日后学习查看。REPLACE()可以允许使用一个新的字符串替换表中列的指定字符串，并返回这个替换后的字符串。可以用于批量纠正拼写错误、更新字段内容等。REPLACE()函数就是用来做替换操作的，可以手动批量修改一组数据中的错误或过时文本信息，也可以用于数据挖掘和清洗，将输入的字符串中需要替换的数据替换为新的内容，以后续的分析和建模。
MySQL的Replace into 与Insert into on duplicate key update真正的不同之处
09-10
MySQL中的`REPLACE INTO`和`INSERT INTO ... ON DUPLICATE KEY UPDATE`是两种处理数据冲突的方法，它们在处理已有键值冲突时的行为有所不同。这两种语句都是在插入新记录时，如果发现存在相同的唯一键值，则采取不同...
mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析
09-08
在MySQL中，`REPLACE INTO` 和 `INSERT INTO ON DUPLICATE KEY UPDATE` 是两种处理数据插入时冲突的方法，主要用于处理当试图插入一条记录时，若已有相同唯一键值的记录存在，应该如何操作的情况。 1. **REPLACE ...
insert into … on duplicate key update / replace into 多行数据介绍
09-10
ON DUPLICATE KEY UPDATE` 和 `REPLACE INTO` 是MySQL中处理批量插入并更新已有数据的两种有效策略，它们提供了在保持数据完整性和性能之间平衡的解决方案。根据具体业务需求和数据约束，选择合适的方法来优化数据...
Mysql中Insert into xxx on duplicate key update问题
12-15
INSERT INTO table(a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1; UPDATE table SET c = c + 1 WHERE a = 1; ``` 当尝试插入的记录 `(1, 2, 3)` 中的 `a` 值已存在于表中时，`ON DUPLICATE KEY ...
mysql查询replace用法详解
HD243608836的博客
10-20 2万+
一、基本 语法 REPLACE ( string_expression , string_pattern , string_replacement ) 参数 string_expression 要搜索的字符串表达式。string_expression 可以是字符或二进制数据类型。 string_pattern 是要查找的子字符串。string_pattern 可以是字符或二进制数据类型。string_pattern 不能是空字符串 ('')。 string_replacement 替换字符串。
Mysql REPLACE INTO 用法
了解—学习—进步—满足
05-06 2万+
一、replace(object,search,replace) 把object中出现search的全部替换为replace select replace('www.jb51.net','w','n') from ... 例：把表table中的name字段中的detail替换为description update table set name=replace(name,'detail','description') 二、replace into replace into 主要作用类似inse
mysql特殊语法insert into .. on duplicate key update ..使用详解
健身变秃，coding变强
10-21 2万+
mysql特殊语法insert into .. on duplicate key update ..使用详解
MySQL replace into行为解析
yhj198927的博客
05-25 1762
1、不冲突，replace执行语句为insert。2、只存在主键或只存在唯一索引冲突，replace执行update。3、同时存在主键和唯一索引，唯一索引冲突，replace执行update。4、同时存在主键和唯一索引，主键冲突，replace先delete再insert。5、同时存在主键和唯一索引冲突，同一行数据，replace先delete再insert。6、同时存在主键和唯一索引冲突，不同一行数据，replace先delete再update。
MySQL的Replace用法详解
ixxqq的博客
01-07 3万+
replace into平时在开发中很少用到，这次是因为在做一个生成分布式ID的开源项目，调研雅虎推出的一个基于数据库生成唯一id生成方案：flickr 碰到的一个知识盲点，仅以此篇记录一...
Mysql数据库insert、replace、update、delete、truncate、drop、select语句介绍
树下一少年的博客
05-31 7917
Mysql数据库insert、replace、update、delete、truncate、drop、select语句介绍
MySQL INSERT ... ON DUPLICATE KEY UPDATE
只是甲的博客
05-25 8237
备注:测试数据库版本为MySQL 8.0 真实环境中会有一种应用场景，如存在更新，不存在则更新，Oracle中有merge语句，可以实现此功能。 MySQL中可以用INSERT … ON DUPLICATE KEY UPDATE来实现。 文章目录一.从一个简单的例子看INSERT ... ON DUPLICATE KEY UPDATE二.模拟一个同事存在insert+update的例子 一.从一个简单的例子看INSERT … ON DUPLICATE KEY UPDATE 代码: create table
insert...on duplicate key update语法详解
热门推荐
赵英超的博客
02-10 4万+
一.作用和使用场景 在mysql入库时，不能出现两条数据主键一致的情况，因为在两条数据的主键一致的情况下，mysql就会判定为待插入数据在数据库中存在重复数据，也就是说判断数据是否重复是根据主键来区别的。 但是有一些场景，如日志文件解析入库，消息队列接收数据入库等情况下可能解析到或者接收到待插入的重复数据存在重复数据则更新，不存在则插入。 这时如下语句的写法就派上用场了，on dupli...
ON DUPLICATE KEY UPDATE REPLACE INTO性能分析
04-24
- ON DUPLICATE KEY UPDATE执行更新操作，而REPLACE INTO执行删除和插入操作。 - ON DUPLICATE KEY UPDATE只更新重复键所在行的列值，而REPLACE INTO插入完全新的数据。 - ON DUPLICATE KEY UPDATE保留了其他列的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值