AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL】replace into 浅析之一

2015-05-24 1272
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
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原理，讲述生产过程中的注意事项。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版into
云数据库 RDS MySQL 版replace
云数据库 RDS MySQL 版replace into
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
yangyi_dba
目录
相关文章
wljslmz
|
3月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用 `REPLACE` 函数
【8月更文挑战第8天】
wljslmz
912 7
在 MySQL 中使用 `REPLACE` 函数
wljslmz
|
3月前
|
存储 关系型数据库 MySQL
在 MySQL 中使用 Insert Into Select
【8月更文挑战第11天】
wljslmz
582 0
在 MySQL 中使用 Insert Into Select
红目香薰
|
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）
红目香薰
118 0
讓丄帝愛伱
|
Oracle 关系型数据库 MySQL
mysql replace into 缺陷的解决方案
mysql replace into 缺陷的解决方案
讓丄帝愛伱
236 0
讓丄帝愛伱
|
关系型数据库 MySQL
mysql中replace into的用法
mysql中replace into的用法
讓丄帝愛伱
92 0
陈宗志
|
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
陈宗志
428 0
修己xj
|
存储 关系型数据库 MySQL
MySQL中的INSERT INTO SELECT语法及其用法详解
当今的数据库管理系统在数据存储和检索方面起着关键作用，而MySQL作为最受欢迎的开源关系型数据库管理系统之一，提供了许多强大的功能。在MySQL中，INSERT INTO SELECT语法是一种非常有用的功能，可以将查询结果直接插入到目标表中。本文将介绍MySQL中的INSERT INTO SELECT语法及其用法。
修己xj
795 0
翎野君
|
关系型数据库 MySQL 索引
Mysql中REPLACE INTO用法，判断数据是否存在，如果不存在，则插入，如果存在，则先删除此行数据，然后插入新的数据...
Mysql中REPLACE INTO用法，判断数据是否存在，如果不存在，则插入，如果存在，则先删除此行数据，然后插入新的数据...
翎野君
434 0
杨奇龙
|
6月前
|
监控 关系型数据库 MySQL
MySQL 并发 replace 导致的死锁
一 前言死锁其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文是源于生产过程中一个死锁案例。二 背景知识官方文档[1]中表述:"REPLACE is done like an INS...
杨奇龙
185 0
路卿老师
|
13天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
28 1

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    C语言连接MySQL数据库
  • 4
    JDBC操作MySQL5日期类型字段的问题解决方法
  • 5
    MySQL的innodb_flush_log_at_trx_commit配置值的设定
  • 6
    PG+MySQL第11课-多维向量搜索
  • 7
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 8
    Mariadb/Mysql TEMPORARY表
  • 9
    云原生数据仓库产品使用合集之ADB MySQL湖仓版和 StarRocks 的使用场景区别，或者 ADB 对比 StarRocks 的优劣势
  • 10
    在华为鲲鹏OpenEuler20.03系统上安装MySQL8
  • 1
    服务器Linux系统配置mysql数据库主从自动备份
    202
  • 2
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    413
  • 3
    golang使用gorm操作mysql3，数据查询
    90
  • 4
    golang使用gorm操作mysql2
    55
  • 5
    golang使用gorm操作mysql1
    102
  • 6
    Hbase与MySQL对比，区别是什么？
    278
  • 7
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2102
  • 8
    navicat过期了，直接用idea连接mysql
    58
  • 9
    DQL操作MySql
    49
  • 10
    【JAVA进阶篇教学】第八篇：Java链接MySql数据库异常
    250

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    无影云桌面