mysql into @_MySQL replace into 语句浅析（一）

最新推荐文章于 2024-03-19 16:26:32 发布
最新推荐文章于 2024-03-19 16:26:32 发布
阅读量151 收藏
点赞数
文章标签： mysql into @
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31787547/article/details/113294024
版权

一 介绍

在笔者支持业务过程中，经常遇到开发咨询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版本均做过测试】

b1ac3aedd35ba8d8d4469671cbf5f379.png

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

黄亦阳
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql中replace into的用法详解
12-14
replace into语句会根据唯一键/主键删除原有的一条记录， 并且插入一条新的记录来替换原记录。如果没有表不存在唯一键/主键 或者 插入的数据唯一键/主键没有重复的，那么会新增一条记录。 新建一个test表，三个字段，uid，name，mobile, id是自增的主键，mobile是唯一索引； create table test( uid int(11) NOT NULL AUTO_INCREMENT, name varchar(50), mobile varchar(11) unique, PRIMARY KEY (uid) ); #插入两条数据进行演示 i
mysql into @_MYSQL中replace into的用法
weixin_29421255的博客
01-20 159
新建一个test表，三个字段，id，title，uid, id是自增的主键，uid是唯一索引；插入两条数据insert into test(title,uid) VALUES ('123465','1001');insert into test(title,uid) VALUES ('123465','1002');执行单条插入数据可以看到，执行结果如下：[SQL]insert into ...
MySQL中replace into详解、批量更新、不存在插入存在则更新、replace into的坑
最新发布
weixin_49114503的博客
03-19 2981
一、replace into原理 二、`replace into`的三种形式 三、replace into 使用案例 3.1、replace into values 3.1.1、只有主键且主键冲突 3.1.2、有主键有唯一索引且主键冲突 3.1.3、有主键有唯一索引且唯一索引冲突(有坑) 3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑) 3.2、replace into select 3.3、replace into set 3.3.1、唯一索引不重复 3.3.2、唯一索引重复 四、r
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 replace into 语句浅析（一）
09-10
主要介绍了MySQL replace into 语句浅析（一）,本文讲解了replace into的原理、使用方法及使用的场景和使用示例,需要的朋友可以参考下
MySQL replace into 语句浅析（二）
09-10
主要介绍了MySQL replace into 语句浅析（二）,本文着重给出了几个特殊案例分析,需要的朋友可以参考下
MySQL中replace into语句的用法详解
09-10
主要介绍了MySQL中replace into语句的用法详解,本文讲解了replace into语句的多种写法,replace into语句的作用等内容,需要的朋友可以参考下
MySQL replace into行为解析
yhj198927的博客
05-25 1708
1、不冲突，replace执行语句为insert。2、只存在主键或只存在唯一索引冲突，replace执行update。3、同时存在主键和唯一索引，唯一索引冲突，replace执行update。4、同时存在主键和唯一索引，主键冲突，replace先delete再insert。5、同时存在主键和唯一索引冲突，同一行数据，replace先delete再insert。6、同时存在主键和唯一索引冲突，不同一行数据，replace先delete再update。
mysql replaceinto_Mysql中replace into用法详细说明
weixin_32211137的博客
01-20 614
Replace into是Insert into的增强版。在向表中插入数据时，我们经常会遇到这样的情况：1、首先判断数据是否存在；2、如果不存在，则插入；3、如果存在，则更新。在SQL Server中可以这样处理：if not exists (select 1 from t where id = 1)insert into t(id, update_time) values(1, getdate(...
MySQL中REPLACE INTO语句的用法详解
zhangyisc的专栏
01-20 854
replae into的详解
MySQL之replace into
qq_42417269的博客
11-21 1275
replace into和insert into的区别，以及replace into的基本知识点
mysql中insert into语句的6种写法（上）
热门推荐
number1killer的博客
09-04 21万+
insert into是mysql中最常用的插入语句，它有6种写法。下面让我们一起来看一看吧： mysql中insert into语句的6种写法（下） http://blog.csdn.net/number1killer/article/details/77842472 1向原表中某些字段中插入一条记录。 语法：insert into +表名（表中的字段，，）value...
MySQL REPLACE INTO三种形式
心有山海静而无边
08-03 2734
我们在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在；2. 如果不存在，则插入；3.如果存在，则更新。包括我在内的程序猿们常见的做法有三种： 第一种：MySQL很常见的一种做法，许多新手、甚至许多资深的高级coder也有这么写的，会在代码中封装三个函数，一个函数查询记录是否存在，一个函数实现直接插入，另一个函数对已有记录进行更新。在不同的情况进行调用。这种方法多次excu...
replace into
bobo now~
09-02 2486
replace into的用法，真的很好用，是insert into的增强版。在向表中插入数据时，我们经常会遇到这样的情况：1、首先判断数据是否存在；2、如果不存在，则插入；3、如果存在，则更新。 在SQL Server中可以这样处理： if not exists (
mysql 中 replace into 语法解释
qq_42449963的博客
09-07 969
插入数据时，首先根据 主键 或者 唯一索引 判断表中是否有重复数据。
MySQL replace into 用法（insert into 的增强版）
risingsun001的专栏
09-01 12万+
MySQL replace into 用法（insert into 的增强版） 在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在； 2. 如果不存在，则插入；3.如果存在，则更新。 在 SQL Server 中可以这样处理： if not exists (select 1 from t where id = 1) insert into t(i
MySQL REPLACE INTO语句
牛奔的博客
10-14 413
介绍 在向表中插入数据时，我们经常会：首先判断数据是否存在；如果不存在，则插入；如果存在，则更新。 但在 MySQL 中有更简单的方法，replace into(insert into 的增强版)，当表中的旧行与 PRIMARY KEY或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行，否则只就新增插入。 REPLACE 它的工作方式与 INSERT 完全相同，它是 SQL 标准...
mysql replace into 锁_replace into 引发的死锁问题
06-10
MySQL中的`REPLACE INTO`语句会先尝试插入一条新记录，如果该记录在表中已存在，则会先删除该记录，然后再插入一条新记录。在多线程环境下，如果多个线程同时执行`REPLACE INTO`语句，并且它们都要插入同一条记录，就会引发死锁问题。 这是因为，当一个线程在执行`REPLACE INTO`语句时，会先获取该记录的行级写锁，然后再删除该记录并插入新记录，这个过程中会持有该行的写锁。如果另一个线程也要插入同一条记录，就需要等待该行的写锁被释放。但是如果该线程在等待期间又执行了`REPLACE INTO`语句，就会尝试获取该行的写锁，从而形成了死锁。 为了避免这个问题，可以考虑使用`INSERT INTO ... ON DUPLICATE KEY UPDATE`语句，它会先尝试插入一条新记录，如果该记录在表中已存在，则会执行更新操作。该语句在执行时只会获取该行的行级读锁，而不会持有行级写锁，因此不会引发死锁问题。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值