开发者社区> 北在南方> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【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语句
结论 如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为会导致主键的重新组织 当存在pk冲突的时候是先delete再insert 当存在uk冲突的时候是直接update,update操作不会涉及到auto_increment的修改 很大程度上会导致主备中断,存在容灾.
3308 0
SQL中的replace函数
REPLACE ( string_replace1 , string_replace2 , string_replace3 ) 参数解析: string_replace1 待搜索的字符串表达式。
1065 0
第179天:javascript中replace使用总结
ECMAScript提供了replace()方法。这个方法接收两个参数,第一个参数可以是一个RegExp对象或者一个字符串,第二个参数可以是一个字符串或者一个函数。现在我们来详细讲解可能出现的几种情况。
938 0
MySQL 小心使用 replace into
MySQL replace into 错误案例 背景 * MySQL5.7 * ROW模式 * 表结构 CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `col_1` varc
8572 0
MySQL replace into (insert into 的增强版)
在使用SQL语句进行数据表插入insert操作时,如果表中定义了主键,插入具有相同主键的记录会报错:    Error Code: 1062. Duplicate entry 'XXXXX' for key 'PRIMARY'(主键冲突)   这样我们只好检查主键是不是存在,若存在则更新,若不存在则插入。
682 0
+关注
640
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载