【MySQL】replace into 浅析之二

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一 介绍   上一篇文章介绍了replace into的基本原理。本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的情况发生。
一 介绍
  上一篇文章介绍了replace into的基本原理。本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的情况发生。
二 案例分析
在主库上操作
  1. root@test 12:36:51>show create table t1 \G
  2. *************************** 1. row ***************************
  3.        Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.   `id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   UNIQUE KEY `name` (`name`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  10. 1 row in set (0.00 sec)
  11. root@test 12:37:41>insert into t1(name) values('a')
此时检查主备库上t1的表结构都是一样的, AUTO_INCREMENT 都是2.
  1. root@test 12:37:51>show create table t1 \G
  2. *************************** 1. row ***************************
  3.        Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.   `id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   UNIQUE KEY `name` (`name`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
  10. 1 row in set (0.00 sec)
在主库上进行进行replace into操作
  1. root@test 12:37:58>replace into t1(name) values('a');
  2. root@test 12:38:40>replace into t1(name) values('a');
  3. root@test 12:38:49>select * from t1;
  4. +----+------+
  5. | id | name |
  6. +----+------+
  7. | 3 | a |
  8. +----+------+
  9. 1 row in set (0.00 sec)
此时检查主备库中t1 表结构,请注意 AUTO_INCREMENT=4

  1. root@test 12:38:51>show create table t1 \\G
  2. *************************** 1. row ***************************
  3.        Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.   `id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   UNIQUE KEY `name` (`name`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
  10. 1 row in set (0.00 sec)
从库上t1的表结构 , AUTO_INCREMENT=2  
  1. root@test 12:39:35>show create table t1 \G
  2. *************************** 1. row ***************************
  3.        Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.   `id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   UNIQUE KEY `name` (`name`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
  10. 1 row in set (0.00 sec)
  11. root@test 12:39:43>select * from t1;
  12. +----+------+
  13. | id | name |
  14. +----+------+
  15. | 3 | a |
  16. +----+------+
  17. 1 row in set (0.00 sec)
【分析】
  表t1的表结构 AUTO_INCREMENT=2 而主库上的t1表结构的AUTO_INCREMENT=4.原本replace操作是在自增主键的情况下,遇到唯一键冲突时执行的是delete+insert,但是在记录binlog时,却记录成了update操作,update操作不会涉及到auto_increment的修改。 备库应用了binlog之后,备库的表的auto_increment属性不变。
三 风险点:
  如果主备库发生主从切换,备库变为原来的主库,按照原来的业务逻辑再往下会发生什么?
root@test 12:40:46>replace into t1(name) values('a');  
Query OK, 2 rows affected (0.00 sec)
root@test 12:40:48>select * from t1;
+----+------+
| id | name |
+----+------+
|   2 | a    |  ---id由原来的3变成了2.
+----+------+
1 row in set (0.00 sec)
如果t1表本来就存在多条记录 ,主从切换之后,应用写新的主库则会发生主键冲突,这个留给各位读者自己测试一下。^_^
四 解决方法
  推荐使用 insert into table values(,....) on duplicate key update 语法结构来解决业务需求。 来避免replace into 给含有自增主键的表带来主从不一致情况。
root@test 01:14:28>insert into t1(name) values('a');
Query OK, 1 row affected (0.00 sec) root@test 01:14:51>insert into t1(name) values('b');
Query OK, 1 row affected (0.00 sec) root@test 01:14:54>insert into t1(name) values('c');
Query OK, 1 row affected (0.00 sec) root@test 01:14:57>select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec) root@test 01:16:17>show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) root@test 01:16:26>insert into tt(name) values('a') on duplicate key update name='a';
Query OK, 1 row affected (0.00 sec) root@test 01:17:09>show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
五 总结
  由于replace into操作在遇到主键冲突的时候 会修改主键的值,所以如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为replace into 操作可能会导致主键的重新组织.推荐使用  insert into table values(,....) on duplicate key update 来解决业务需求。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
10月前
|
关系型数据库 MySQL
MySQL实战基础知识入门(4):MySQL高级函数CASE WHEN END
MySQL实战基础知识入门(4):MySQL高级函数CASE WHEN END
73 0
|
6月前
|
关系型数据库 MySQL
mysql中replace into的用法
mysql中replace into的用法
|
关系型数据库 MySQL 数据库
MySQL(十一)
MySQL(十一),一起来学习吧。
|
关系型数据库 MySQL
mysql:LIKE和REGEXP操作有什么区别?
mysql:LIKE和REGEXP操作有什么区别?
85 0
|
关系型数据库 MySQL 测试技术
软件测试mysql面试题:mysql中 in 和 exists 区别?
软件测试mysql面试题:mysql中 in 和 exists 区别?
101 0
|
关系型数据库 数据库管理 MySQL
MySQL 小心使用 replace into
MySQL replace into 错误案例 背景 * MySQL5.7 * ROW模式 * 表结构 CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `col_1` varc
9595 0
|
SQL 关系型数据库 MySQL
《零基础》MySQL GROUP BY 语句(十九)
GROUP BY 语句根据一个或多个列对结果集进行分组。 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。 GROUP BY 语法
93 0
|
SQL 关系型数据库 MySQL
《零基础》MySQL DELETE 语句(十五)
法 以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法: DELETE FROM table_name [WHERE Clause]
99 0
|
存储 SQL 缓存
MySQL 深潜 - 一文详解 MySQL Data Dictionary
# 背景 在 MySQL 8.0 之前,Server 层和存储引擎(比如 InnoDB)会各自保留一份元数据(schema name, table definition 等),不仅在信息存储上有着重复冗余,而且可能存在两者之间存储的元数据不同步的现象。不同存储引擎之间(比如 InnoDB 和 MyISAM)有着不同的元数据存储形式和位置(.FRM, .PAR, .OPT, .TRN and .TR
340 0
|
SQL Oracle 算法
Oracle MySQL相关工具replace操作实现方式区别
本文列举MySQL、Oracle、常用数据导入工具碰到主键冲突、唯一约束、以及其他对重复数据、已有数据的处理区别,并介绍MySQL、Oracle replace相关语句实现区别。 由此可见在实际使用不同数据库过程中,还是有许多差别,需要大家细心留意。
740 0