设为首页收藏本站
查看: 105|回复: 0

[PHP] MySQLreplaceinto语句浅析(二)_MySQL-mysql教程

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    一 介绍
      上一篇文章介绍了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的表结构都是一样的, 代码如下:
    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 表结构,请注意 代码如下:
    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的表结构 , 代码如下:
    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的修改。 代码如下:
    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 来解决业务需求。
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表