ITPub博客

首页 > 数据库 > MySQL > 【MySQL】replace into 浅析之二

【MySQL】replace into 浅析之二

原创 MySQL 作者:杨奇龙 时间:2015-05-25 02:05:58 2 删除 编辑
一 介绍
  上一篇文章介绍了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表本来就存在多条记录 ,主从切换之后,应用写新的主库则会发生主键冲突,这个留给各位读者自己测试一下。^_^

四 总结

  由于replace into操作在遇到主键冲突的时候 会修改主键的值,所以如果业务逻辑强依赖自增ID,绝对不要用replace,普通环境也不建议这样用,因为replace into 操作可能会导致主键的重新组织.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-1670120/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7472793