慎用mysql replace语句

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 结论 如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为会导致主键的重新组织 当存在pk冲突的时候是先delete再insert 当存在uk冲突的时候是直接update,update操作不会涉及到auto_increment的修改 很大程度上会导致主备中断,存在容灾.

结论

  • 如果业务逻辑强依赖自增ID,建议不要用REPLACE
  • 当存在PK冲突的时候是先DELETE再INSERT
  • 当存在UK冲突的时候是直接UPDATE,UPDATE操作不会涉及到AUTO_INCREMENT的修改
  • 很大程度上会导致主备中断,存在容灾风险

REPLACE的语法

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]  
    [(col_name,...)]
    SELECT ...

原理

REPLACE的工作机制有点像INSERT,只不过如果在表里如果一行有PRIMARY KEY或者UNIQUE索引,那么就会把老行删除然后插入新行。如:

root@test 03:23:55>show create table lingluo\G
*************************** 1. row ***************************
       Table: lingluo
Create Table: CREATE TABLE `lingluo` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),--------------------------同时存在PK约束
  UNIQUE KEY `uk_bc` (`b`,`c`)----------------唯一索引约束
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)
 
root@test 02:01:44>select * from lingluo;
Empty set (0.00 sec)
 
root@test 03:27:40>replace into lingluo values(1,10000,3,4);--------表里没有已存在的记录相当于insert
Query OK, 1 row affected (0.00 sec)-----------------------affect_rows是1
binlog格式:

getTfsFile_1_

root@test 02:11:18>replace into lingluo values(1,10000,3,5);-------已经存在记录,且PK和UK同时冲突的时候,相当于先delete再insert
Query OK, 2 rows affected (0.00 sec)----------------------affect_rows是2,是delete和insert行数的总和
binlog格式:

getTfsFile_2_

root@test 02:26:09>select * from lingluo;
+---+-------+------+------+
| a | b     | c    | d    |
+---+-------+------+------+
| 1 | 10000 |    3 |    5 |
+---+-------+------+------+
1 row in set (0.00 sec)
 
root@test 02:31:54>replace into lingluo values(1,10000,4,5);-------已经存在记录,且PK同时冲突的时候,相当于先delete再insert
Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2,是delete和insert行数的总和
 
root@test 02:32:02>select * from lingluo;
+---+-------+------+------+
| a | b     | c    | d    |
+---+-------+------+------+
| 1 | 10000 |    4 |    5 |
+---+-------+------+------+
binlog格式:

getTfsFile_3_

root@test 02:37:04>replace into lingluo values(4,10000,6,5);
Query OK, 1 row affected (0.00 sec)
root@test 02:37:59>replace into lingluo values(6,10000,6,5);-------已经存在记录,且UK同时冲突的时候,直接update
Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2
 
root@test 02:40:31>select * from lingluo;
+---+-------+------+------+
| a | b     | c    | d    |
+---+-------+------+------+
| 1 | 10000 |    4 |    5 |
| 3 | 10000 |    5 |    5 |
| 6 | 10000 |    6 |    5 |
+---+-------+------+------+
3 rows in set (0.00 sec)
binlog格式:

getTfsFile_4_
这样的风险点:
尽管主备库数据是一致的,但是主备库切换后,备库因AUTO_INCREMENT小于实际数据的最大值,这样会导致写入失败,失败一次后,会更新AUTO_INCREMENT为最大值+1;所以,一些REPLACE操作建议使用INSERT INTO tbname ... VALUES ... ON DUPLICATE KEY UPDATE col1=

建议:

如果业务逻辑强依赖自增ID,绝对不要用REPLACE,普通环境也不建议这样用,因为会导致主键的重新组织

疑问:

既然UK冲突的时候是UPDATE,那么为什么affect_rows都是2呢?让我们从源码上分析看下:

指定列REPLACE:
root@test 03:34:37>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    1 |  126 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
+----+------+------+
6 rows in set (0.00 sec)
 
root@test 03:34:37>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    1 |  126 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
+----+------+------+
6 rows in set (0.00 sec)
 
root@test 03:34:40>replace into u (age,d)values(0,130);
Query OK, 2 rows affected, 1 warning (0.01 sec)
 
root@test 03:40:39>show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
 
root@test 03:40:47>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    0 |  130 |-----------------因为id是parimary但是没有auto_creasement,由126变成130
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
+----+------+------+
6 rows in set (0.00 sec)

用的时候需要注意的是:

如果指定REPLACE列的话,尽量写全,要不然没有输入值的列数据会被赋成默认值(因为是先DELETE在INSERT),就和普通的INSERT是一样的,所以如果你要执行REPLACE语句的话是需要INSERT和DELETE权限的。

如果你需要执行 SET col_name = col_name + 1,就相当于执行col_name = DEFAULT(col_name) + 1.

REPLACE语句如果不深入看的话,就和INSERT一样,执行完后没什么反应。例:

root@test 04:20:04>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
+----+------+------+
6 rows in set (0.00 sec)
 
root@test 04:20:10>replace into u (id,d) values(8,232);
Query OK, 1 row affected (0.01 sec)
 
root@test 04:20:39>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 |    2 |  129 |
|  8 | NULL |  232 |
+----+------+------+
7 rows in set (0.00 sec)
 
root@test 04:20:43>replace into u (id,d) values(7,232);
Query OK, 3 rows affected (0.01 sec)----------注意这里affect_rows是3,因为主键7已经存在,唯一索引232已经存在,所以需要删除id为7和8的行,然后插入新行
 
root@test 04:20:52>select * from u;
+----+------+------+
| id | age  | d    |
+----+------+------+
|  0 |    0 |  130 |
|  1 |    0 |    1 |
|  3 |    1 |  123 |
|  4 |    1 |  127 |
|  5 |    0 |   12 |
|  7 | NULL |  232 |
+----+------+------+
6 rows in set (0.00 sec)
 
root@test 04:20:55>

MySQL给REPLACE和LOAD DATA....REPLACE用的算法是:

  1. 尝试向表里插入新行
  2. 当表里唯一索引或者PRIMARY KEY冲突的时候:
  • DELETE冲突行
  • 往表里再次插入新行

如果遇到重复行冲突,存储过程很可能当作UPDATE执行,而不是DELETE+INSERT,但是显式上都是一样的。这里没有用户可见的影响除了存储引擎层Handler_xxx的状态变量。

因为REPLACE ... SELECT语句的结果依赖于SELECT的行的顺序,但是顺序没办法保证都是一样的,有可能从MASTER和SLAVE的都不一样。正是基于这个原因,MySQL 5.6.4以后,REPLACE ... SELECT语句被标记为基于STATEMENT的复制模式不安全的。基于这个变化,当使用STATEMENT记录二进制日志的时候,如果有这样的语句就会在log里面输出一个告警,同样当使用MIXED行复制模式也会记录告警。

在MySQL5.6.6之前的版本,REPLACE影响分区表就像MyISAM使用表级锁锁住所有的分区表一样。当使用 REPLACE ... PARTITION语句时确实会发生上述情况。(使用基于行锁的InnoDB引起不会发生这种情况。)在MySQL 5.6.6以后的版本MySQL使用分区锁,只有当分区(只要没有分区表的列更新)包含了REPLACE语句并且WHERE实际匹配到的才会锁住那个分区;否则的话就会锁住整个表。

操作形式:
getTfsFile_5_
binlog格式:
getTfsFile_6_

参考

https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
https://dev.mysql.com/doc/refman/5.6/en/replace.html

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用 `REPLACE` 函数
【8月更文挑战第8天】
860 7
在 MySQL 中使用 `REPLACE` 函数
|
3月前
|
SQL 关系型数据库 MySQL
SQL语句编写的练习(MySQL)
这篇文章提供了MySQL数据库中关于学生表、课程表、成绩表和教师表的建表语句、数据插入示例以及一系列SQL查询练习,包括查询、排序、聚合和连接查询等操作。
|
6月前
|
SQL 存储 缓存
SQL语句在MySQL中是如何执行的
SQL语句在MySQL中是如何执行的
79 0
|
SQL 存储 缓存
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
|
Oracle 关系型数据库 MySQL
mysql replace into 缺陷的解决方案
mysql replace into 缺陷的解决方案
220 0
|
关系型数据库 MySQL
mysql中replace into的用法
mysql中replace into的用法
|
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
419 0
|
关系型数据库 MySQL 索引
Mysql中REPLACE INTO用法,判断数据是否存在,如果不存在,则插入,如果存在,则先删除此行数据,然后插入新的数据...
Mysql中REPLACE INTO用法,判断数据是否存在,如果不存在,则插入,如果存在,则先删除此行数据,然后插入新的数据...
426 0
|
存储 SQL 关系型数据库
MySQL数据库系列(八)-------高级语句二
可以被当作是虚拟表或存储查询
92 0
MySQL数据库系列(八)-------高级语句二
|
SQL 关系型数据库 MySQL

相关产品

  • 云数据库 RDS MySQL 版