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

【MySQL】replace into 浅析之一

2016-04-13 1587
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介：
一 介绍
  在笔者支持业务过程中，经常遇到开发咨询replace into 的使用场景以及注意事项，这里做个总结。从功能原理，性能和注意事项上做个说明。
二 原理
2.1 当表中存在主键但是不存在唯一建的时候。

表结构


CREATE TABLE `yy` (
 
  `id` bigint(20) NOT NULL,

  `name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8; 


root@test 02:43:58>insert into yy values(1,'abc');
 
Query OK, 1 row affected (0.00 sec)

root@test 02:44:25>replace into yy values(2,'bbb');

Query OK, 1 row affected (0.00 sec)

root@test 02:55:42>select * from yy;

+----+------+

| id | name |

+----+------+

| 1 | abc |

| 2 | bbb |

+----+------+

2 rows in set (0.00 sec)

root@test 02:55:56>replace into yy values(1,'ccc');

Query OK, 2 rows affected (0.00 sec)

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

### UPDATE test.yy
 
### WHERE

### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */

### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

### SET

### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */

### @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 当表中主键和唯一键同时存在时 


CREATE TABLE `yy` (
 
  `id` int(11) NOT NULL DEFAULT \'0\',

  `b` int(11) DEFAULT NULL,

  `c` int(11) DEFAULT NULL 

  PRIMARY KEY (`a`), 

  UNIQUE KEY `uk_bc` (`b`,`c`) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8

情形1 主键冲突 


root@test 04:37:18>replace into yy values(1,2,3);
 
Query OK, 1 row affected (0.00 sec)

root@test 04:37:37>replace into yy values(2,2,4);

Query OK, 1 row affected (0.00 sec)

root@test 04:38:05>select * from yy;

+----+------+------+

| id | b | c |

+----+------+------+

| 1 | 2 | 3 |

| 2 | 2 | 4 |

+----+------+------+

2 rows in set (0.00 sec)

root@test 04:38:50>replace into yy values(1,2,5);

Query OK, 2 rows affected (0.00 sec)

root@test 04:38:58>select * from yy;

+----+------+------+

| id | b | c |

+----+------+------+

| 2 | 2 | 4 |

| 1 | 2 | 5 |

+----+------+------+

2 rows in set (0.00 sec)

主键冲突时，数据库对表做 先删除然后插入的操作，也即先删除id=1的记录，然后插入新的id=1 的记录(1,2,5). 


BINLOG '
 
Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA

### DELETE FROM test.yy

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=2 /* INT meta=0 nullable=1 is_null=0 */

### @3=3 /* INT meta=0 nullable=1 is_null=0 */

Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA

'/*!*/;

### INSERT INTO test.yy

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=2 /* INT meta=0 nullable=1 is_null=0 */

### @3=5 /* INT meta=0 nullable=1 is_null=0 */

# at 662

#150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508

COMMIT/*!*/

情形2 唯一建冲突 


root@test 04:48:30>select * from yy;
 
+----+------+------+

| id | b | c |

+----+------+------+

| 1 | 2 | 4 |

| 2 | 2 | 5 |

| 3 | 3 | 5 |

| 4 | 3 | 6 |

+----+------+------+

4 rows in set (0.00 sec)

root@test 04:53:21>replace into yy values(5,3,6);

Query OK, 2 rows affected (0.00 sec)

root@test 04:53:40>select * from yy;

+----+------+------+

| id | b | c |

+----+------+------+

| 1 | 2 | 4 |

| 2 | 2 | 5 |

| 3 | 3 | 5 |

| 5 | 3 | 6 |

+----+------+------+

4 rows in set (0.00 sec)

主键不冲突，唯一键冲突时，数据库对表 唯一键为(3，6)的行做update操作，将主键修改为要插入的值，id=4 改为id=5。 


BINLOG \'
 
lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=

\'/*!*/;

### UPDATE test.yy

### WHERE

### @1=4 /* INT meta=0 nullable=0 is_null=0 */

### @2=3 /* INT meta=0 nullable=1 is_null=0 */

### @3=6 /* INT meta=0 nullable=1 is_null=0 */

### SET

### @1=5 /* INT meta=0 nullable=0 is_null=0 */

### @2=3 /* INT meta=0 nullable=1 is_null=0 */

### @3=6 /* INT meta=0 nullable=1 is_null=0 */

# at 274

#150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872

COMMIT/*!*/

情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。 


root@test 04:53:52>replace into yy values(1,3,6);
 
Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3 

root@test 04:55:35>select * from yy;

+----+------+------+

| id | b | c |

+----+------+------+

| 2 | 2 | 5 |

| 3 | 3 | 5 |

| 1 | 3 | 6 |

+----+------+------+

3 rows in set (0.00 sec)

  要插入的值(1,3,6) 主键于 表里面的id=1的值冲突，唯一键(3,6)和表中id=5的记录冲突，MySQL 处理的时候 ，先删除id=1的行，然后更新了id=5的行。 


BINLOG \'
 
B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==

B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA

### DELETE FROM test.yy

### WHERE

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=2 /* INT meta=0 nullable=1 is_null=0 */

### @3=4 /* INT meta=0 nullable=1 is_null=0 */

B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=

\'/*!*/;

### UPDATE test.yy

### WHERE

### @1=5 /* INT meta=0 nullable=0 is_null=0 */

### @2=3 /* INT meta=0 nullable=1 is_null=0 */

### @3=6 /* INT meta=0 nullable=1 is_null=0 */

### SET

### @1=1 /* INT meta=0 nullable=0 is_null=0 */

### @2=3 /* INT meta=0 nullable=1 is_null=0 */

### @3=6 /* INT meta=0 nullable=1 is_null=0 */

# at 510

#150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904

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
北在南方
目录
相关文章
wljslmz
|
2月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用 `REPLACE` 函数
【8月更文挑战第8天】
wljslmz
637 7
在 MySQL 中使用 `REPLACE` 函数
wljslmz
|
2月前
|
存储 关系型数据库 MySQL
在 MySQL 中使用 Insert Into Select
【8月更文挑战第11天】
wljslmz
153 0
在 MySQL 中使用 Insert Into Select
红目香薰
|
5月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表10、数据导入和导出——1、数据导入（LOAD DATA, mysqldump）——2、数据导出（SELECT ... INTO OUTFILE, mysqldump）
MySQL技能完整学习列表10、数据导入和导出——1、数据导入（LOAD DATA, mysqldump）——2、数据导出（SELECT ... INTO OUTFILE, mysqldump）
红目香薰
103 0
讓丄帝愛伱
|
11月前
|
Oracle 关系型数据库 MySQL
mysql replace into 缺陷的解决方案
mysql replace into 缺陷的解决方案
讓丄帝愛伱
175 0
讓丄帝愛伱
|
11月前
|
关系型数据库 MySQL
mysql中replace into的用法
mysql中replace into的用法
讓丄帝愛伱
66 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
陈宗志
376 0
修己xj
|
存储 关系型数据库 MySQL
MySQL中的INSERT INTO SELECT语法及其用法详解
当今的数据库管理系统在数据存储和检索方面起着关键作用，而MySQL作为最受欢迎的开源关系型数据库管理系统之一，提供了许多强大的功能。在MySQL中，INSERT INTO SELECT语法是一种非常有用的功能，可以将查询结果直接插入到目标表中。本文将介绍MySQL中的INSERT INTO SELECT语法及其用法。
修己xj
616 0
翎野君
|
关系型数据库 MySQL 索引
Mysql中REPLACE INTO用法，判断数据是否存在，如果不存在，则插入，如果存在，则先删除此行数据，然后插入新的数据...
Mysql中REPLACE INTO用法，判断数据是否存在，如果不存在，则插入，如果存在，则先删除此行数据，然后插入新的数据...
翎野君
414 0
杨奇龙
|
5月前
|
监控 关系型数据库 MySQL
MySQL 并发 replace 导致的死锁
一 前言死锁其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文是源于生产过程中一个死锁案例。二 背景知识官方文档[1]中表述:"REPLACE is done like an INS...
杨奇龙
155 0
叫个什么名字
|
18天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择：MySQL、PostgreSQL 还是 NoSQL？
在微服务架构中，数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中，我们将深入探讨传统的关系型数据库（如 MySQL 和 PostgreSQL）与现代 NoSQL 数据库的优劣势，并分析在微服务架构下的最佳实践。
叫个什么名字
119 9

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    【Mysql】服务没有响应控制功能。 请键入 NET HELPMSG 2186 以获得更多的帮助。
  • 3
    MySQL + Keepalived 双主热备搭建
  • 4
    mysql建库
  • 5
    (解释文)My SQL中主键为0和主键自排约束的关系
  • 6
    centos安装nginx+mysql+php+fastcgi+memcache最简单方法
  • 7
    关于MYSQL的行列转换
  • 8
    使用 EF Power Tool Code Frist 生成 Mysql 实体
  • 9
    MySQL常规日志排错
  • 10
    模拟生产环境MySQL5.5升级Percona5.6演示步骤
  • 1
    为什么MySQL分页查询偏移量越大查询越慢
    209
  • 2
    MySQL的锁机制
    48
  • 3
    【Mysql】在多表查询过程中如果已经起了别名，就不能通过表名限定字段
    66
  • 4
    【MySQL-11】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）
    86
  • 5
    【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
    73
  • 6
    【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
    443
  • 7
    【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
    50
  • 8
    【MySQL-9】一文带你搞定 外键约束＆其【更新/删除行为】（可cv代码＆案例演示）
    163
  • 9
    【MySQL】DQL-案例练习-DQL基本介绍＆语法＆执行顺序（代码演示）
    51
  • 10
    【MySQL】DQL-排序查询-语法＆注意事项＆可cv例题语句
    51

    • 相关课程

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

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

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