【MySQL】replace into 浅析之一

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一 介绍   在笔者支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结。从功能原理,性能和注意事项上做个说明。 二 原理 2.1 当表中存在主键但是不存在唯一建的时候。
一 介绍
  在笔者支持业务过程中,经常遇到开发咨询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原理,讲述生产过程中的注意事项。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
57 0
|
6月前
|
Oracle 关系型数据库 MySQL
mysql replace into 缺陷的解决方案
mysql replace into 缺陷的解决方案
|
6月前
|
关系型数据库 MySQL
mysql中replace into的用法
mysql中replace into的用法
|
9月前
|
存储 关系型数据库 MySQL
MySQL中的INSERT INTO SELECT语法及其用法详解
当今的数据库管理系统在数据存储和检索方面起着关键作用,而MySQL作为最受欢迎的开源关系型数据库管理系统之一,提供了许多强大的功能。在MySQL中,INSERT INTO SELECT语法是一种非常有用的功能,可以将查询结果直接插入到目标表中。本文将介绍MySQL中的INSERT INTO SELECT语法及其用法。
312 0
|
11月前
|
NoSQL 关系型数据库 MySQL
MySQL 常见死锁场景 -- 并发Replace into导致死锁
### MySQL Replace into issue MySQL 并发 Replace into 引起死锁问题 在之前的文章 [#issue 68021 MySQL unique check 问题](https://zhuanlan.zhihu.com/p/503880736)中, 我们已经介绍了在 MySQL 里面, 由于唯一键的检查(unique check), 导致 MySQ
253 0
|
关系型数据库 MySQL 索引
Mysql中REPLACE INTO用法,判断数据是否存在,如果不存在,则插入,如果存在,则先删除此行数据,然后插入新的数据...
Mysql中REPLACE INTO用法,判断数据是否存在,如果不存在,则插入,如果存在,则先删除此行数据,然后插入新的数据...
177 0
|
关系型数据库 MySQL
MySQL: INSERT INTO SELECT 语句实现数据快速复制
MySQL: INSERT INTO SELECT 语句实现数据快速复制
188 0
|
SQL 关系型数据库 MySQL
mysql insert into ... select的锁问题
mysql insert into ... select的锁问题
324 0
|
4天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
79 0
|
6天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
60 0