登录
当前位置： 首页 > 编程笔记 >

MySQL replace into 语句浅析（一）

锺博耘
2023-03-14
本文向大家介绍MySQL replace into 语句浅析（一），包括了MySQL replace into 语句浅析（一）的使用技巧和注意事项，需要的朋友参考一下

一 介绍

  在笔者支持业务过程中，经常遇到开发咨询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原理，讲述生产过程中的注意事项。

 类似资料：
  • MySQL replace into 语句浅析（二）

    本文向大家介绍MySQL replace into 语句浅析（二），包括了MySQL replace into 语句浅析（二）的使用技巧和注意事项，需要的朋友参考一下 一 介绍   上一篇文章介绍了replace into的基本原理。本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的

  • 深入浅析JavaScript中with语句的理解

    本文向大家介绍深入浅析JavaScript中with语句的理解，包括了深入浅析JavaScript中with语句的理解的使用技巧和注意事项，需要的朋友参考一下 JavaScript 有个 with 关键字, with 语句的原本用意是为逐级的对象访问提供命名空间式的速写方式. 也就是在指定的代码区域, 直接通过节点名称调用对象. with语句的作用是暂时改变作用域链、减少的重复输入。 其语法结构为

  • 浅谈Python的条件判断语句if/else语句

    本文向大家介绍浅谈Python的条件判断语句if/else语句，包括了浅谈Python的条件判断语句if/else语句的使用技巧和注意事项，需要的朋友参考一下 计算机之所以能做很多自动化的任务，因为它可以自己做条件判断。 比如，输入用户的年龄，根据年龄打印不同的内容。。。 Python程序中，能让计算机自己作出判断的语句就是if语句： 例： 根据python的缩进规则，如果if语句的条件判断为Tr

  • 浅谈JavaScript异常处理语句

    本文向大家介绍浅谈JavaScript异常处理语句，包括了浅谈JavaScript异常处理语句的使用技巧和注意事项，需要的朋友参考一下 程序运行过程中难免会出错，出错后的运行结果往往是不正确的，因此运行时出错的程序通常被强制中止。运行时的错误统称为异常，为了能在错误发生时得到一个处理的机会，JavaScript提供了异常处理语句。包含try-catch、try-catch-finally和thro

  • Go语言反射规则浅析

    主要内容：Go语言中的类型,反射第一定律：反射可以将“接口类型变量”转换为“反射类型对象”,反射第二定律：反射可以将“反射类型对象”转换为“接口类型变量”,反射第三定律：如果要修改“反射类型对象”其值必须是“可写的”,结构体,总结反射是众多编程语言中的一个非常实用的功能，它是一种能够自描述、自控制的应用，Go语言也对反射提供了友好的支持。 Go语言中使用反射可以在编译时不知道类型的情况下更新变量，在运行时查看值、调用方法以及直接对他们的布局进行操作。 由于反射是建立在类型系统（type syst

  • 浅析Go语言版本的forgery

    本文向大家介绍浅析Go语言版本的forgery，包括了浅析Go语言版本的forgery的使用技巧和注意事项，需要的朋友参考一下 使用过Python语言的朋友们可能使用过 forgery_py ，它是一个伪造数据的工具。能伪造一些常用的数据。在我们开发过程和效果展示是十分有用。但是没有Go语言版本的，所以就动手折腾吧。 从源码入手 在forgery_py的 PyPi 有一段的实例代码： 从以上的方法

相关阅读

浅析Go语言版本的forgery深入浅析react native es6语法浅析char 指针变量char *=p 这个语句的输出问题浅析Go语言中的Range关键字浅析易语言网赚应用心得

相关文章

SQL DELETE语句SQL Update语句SQL INSERT语句SQL SELECT语句Erlang case语句

相关问答

if-ther语句始终在login.php中运行另一语句使用Facebook Presto SQL解析器生成SQL语句如何用WHERE子句中的SELECT语句编写SQL DELETE语句？如何编写switch语句，特别是default语句“缺少return语句”但我已经有return语句

相关工具

开心英语8000句【iphone/ipad版】每日金句隐语神语言Shen语言

相关文档

浅入浅出 Android 安全浅谈 JavaScriptR 统计分析语言 中文教程深入浅出 Node.jsJSP 由浅入深
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.