MySQL replace into 语句浅析(二)

一 介绍

上一篇文章介绍了replace into的基本原理。本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的情况发生。

二 案例分析

在主库上操作

代码如下:

root@test 12:36:51>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 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:37:41>insert into t1(name) values('a')

此时检查主备库上t1的表结构都是一样的,AUTO_INCREMENT 都是2.

代码如下:

root@test 12:37:51>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=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
在主库上进行进行replace into操作
root@test 12:37:58>replace into t1(name) values('a');
root@test 12:38:40>replace into t1(name) values('a');
root@test 12:38:49>select * from t1;
+----+------+
| id | name |
+----+------+
| 3 | a |
+----+------+
1 row in set (0.00 sec)

此时检查主备库中t1 表结构,请注意AUTO_INCREMENT=4

代码如下:

root@test 12:38:51>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)

从库上t1的表结构 ,AUTO_INCREMENT=2

代码如下:

root@test 12:39:35>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=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@test 12:39:43>select * from t1;
+----+------+
| id | name |
+----+------+
| 3 | a |
+----+------+
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 来解决业务需求。

(0)

相关推荐

  • MySQL Replace INTO的使用

    REPLACE的运行与INSERT很相像.只有一点除外,如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除.请参见13.2.4节,"INSERT语法". 注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义.该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行. 所有列的值均取自在REPLACE语句中被指定的值.所有缺失的列被设置为各自的默

  • MySQL中replace into语句的用法详解

    在向表中插入数据的时候,经常遇到这样的情况: 1.首先判断数据是否存在: 2.如果不存在,则插入: 3.如果存在,则更新.   在 SQL Server 中可以这样写: 复制代码 代码如下: if not exists (select 1 from table where id = 1) insert into table(id, update_time) values(1, getdate()) else update table set update_time = getdate() whe

  • 浅析MySQL replace into 的用法

    在 SQL Server 中可以这样处理: 复制代码 代码如下: if not exists (select 1 from t where id = 1)   insert into t(id, update_time) values(1, getdate())else   update t set update_time = getdate() where id = 1 那么 MySQL 中如何实现这样的逻辑呢?别着急!MySQL 中有更简单的方法: replace into 复制代码 代码如

  • mysql 的replace into实例详解

    mysql 的replace into实例详解 replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中. 1.如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据. 2. 否则,直接插入新数据. 要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据.  MySQL中replace into有三种写法: 代码如下: replac

  • MySQL中REPLACE INTO和INSERT INTO的区别分析

    注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义.该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行. [separator] 所有列的值均取自在REPLACE语句中被指定的值.所有缺失的列被设置为各自的默认值,这和INSERT一样.您不能从当前行中引用值,也不能在新行中使用值.如果您使用一个例如"SET col_name = col_name + 1"的赋值,则对位于右侧的列名称的引用会被作为DEFAULT(c

  • MySQL的Replace into 与Insert into on duplicate key update真正的不同之处

    看下面的例子吧: 1 Replace into ...1.1 录入原始数据mysql> use test;Database changedmysql> mysql> CREATE TABLE t1 SELECT 1 AS a, 'c3' AS b, 'c2' AS c;ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_INCREMENT ;Query OK, 1 row affected (0.03 sec)Records: 1  Duplic

  • 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 CH

  • MySQL replace into 语句浅析(二)

    一 介绍 上一篇文章介绍了replace into的基本原理.本章内容通过一个例子说明 replace into 带来的潜在的数据质量风险,当涉及replace into操作的表含有自增主键时,主备切换后会造成数据覆盖等不一致的情况发生. 二 案例分析 在主库上操作 复制代码 代码如下: root@test 12:36:51>show create table t1 \G *************************** 1. row ***************************

  • MySQL replace函数替换字符串语句的用法

    MySQL replace函数我们经常用到,下面就为您详细介绍MySQL replace函数的用法,希望对您学习MySQL replace函数方面能有所启迪. 最近在研究CMS,在数据转换的时候需要用到mysql的MySQL replace函数,这里简单介绍一下. 比如你要将表 tb1里面的 f1字段的abc替换为def UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def'); REPLACE(str,from_str,to_str) 在字符串 str 中所有出现

  • php+MySQL判断update语句是否执行成功的方法

    update语句是PHP+MySQL中常用的操作,判断update语句是否执行成功是其中非常重要的一个环节.本文就以实例展示了php+MySQL判断update语句是否执行成功的方法.分享给大家供大家参考之用.具体方法如下: 代码一: $rs=MySQL_query($sql); if(mysql_affected_rows()) echo "sql执行成功"; else echo "sql执行失败"; 代码二: <?php /* 连接数据库 */ mysql

  • mysql 导出select语句结果到excel文件遇到问题及解决方法

    一.导出数据外部 1)mysql连接+将查询结果输出到文件.在命令行中执行(windows的cmd命令行,mac的终端) mysql -hxx -uxx -pxx -e "query statement" db > file -h:后面跟的是链接的host(主机) -u:后面跟的是用户名 -p:后面跟的是密码 db:你要查询的数据库 file:你要写入的文件,绝对路径 例如: 下面将 sql语句 select * from edu_iclass_areas 的查询结果输出到了 /

  • mysql的sql语句特殊处理语句总结(必看)

    1.更新整张表,如果某一行数据的某一列的值为空,那么让他和另一列字段的值相等 UPDATE ot_tgbz set update_day=if(update_day is null,date,update_day); 2.更新整张表,如果某个字段大于2,那么把他们都更新成2: UPDATE ot_user set xingyun_num=if(xingyun_num > 2,2,xingyun_num); 3.把shop_goods这个表里的thumb字段里的内容中含有http://www.jb

  • 细数MySQL中SQL语句的分类

    1:数据定义语言(DDL)   用于创建.修改.和删除数据库内的数据结构,如:1:创建和删除数据库(CREATE DATABASE || DROP  DATABASE):2:创建.修改.重命名.删除表(CREATE  TABLE || ALTER TABLE|| RENAME TABLE||DROP  TABLE):3:创建和删除索引(CREATEINDEX  || DROP INDEX)   2:数据查询语言(DQL)   从数据库中的一个或多个表中查询数据(SELECT)   3:数据操作语

  • 整理MySql常用查询语句(23种)

    废话不多了,直接贴代码了 一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,<,<>,!=,!>,!<,=>,=< 二查询字符串 SELECT * FROM tb_stu WHERE sname = '小刘' SELECT * FROM tb_stu WHERE sname like '刘%' SELECT * FROM tb_stu WHERE sname like '%程序员' SE

  • 对MySql经常使用语句的全面总结(必看篇)

    下面总结的知识点全是经常用的,全都是干货,好好收藏吧. /* 启动MySQL */ net start mysql /* 连接与断开服务器 */ mysql -h 地址 -P 端口 -u 用户名 -p 密码 /* 跳过权限验证登录MySQL */ mysqld --skip-grant-tables -- 修改root密码 密码加密函数password() update mysql.user set password=password('root'); SHOW PROCESSLIST -- 显

随机推荐