MySQL 对 SQL 有很多扩展,有些用起来很方便,但有一些被误用之后会有性能问题,还会有一些意料之外的副作用,比如 REPLACE INTO。
背景
* MySQL5.7
* ROW模式
* 表结构
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
`col_3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
test表有一个自增的 id 字段作为主键,字段 col_1 有 UNIQUE KEY 做唯一性约束。
错误场景一
其他字段value莫名其妙的没了
- step1 初始化记录
mater:lc> REPLACE INTO test (col_1,col_2,col_3) values('a','a','a');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master:lc> REPLACE INTO test (col_1,col_2,col_3) values('b','b','b');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master:lc> REPLACE INTO test (col_1,col_2,col_3) values('c','c','c');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master > show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
`col_3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
mater > select * from test;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
| 1 | a | a | a |
| 2 | b | b | b |
| 3 | c | c | c |
+----+-------+-------+-------+
3 rows in set (0.00 sec)
- step2 构造错误场景
master:lc> replace into test(col_1,col_2) values('c','cc');
Query OK, 2 rows affected (0.00 sec)
dba:lc> select * from test;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
| 1 | a | a | a |
| 2 | b | b | b |
| 4 | c | cc | NULL |
+----+-------+-------+-------+
3 rows in set (0.00 sec)
- 总结
- col_3 的值,从原来的c,变成了NULL,天呐,数据不见了。 id 也变了。
- 用户原本的需求,应该是如果col_1='c' 存在,那么就改变col_2='cc',其余的记录保持不变,结果id,col_3都变化了
- 解决方案就是:将replace into 改成 INSERT INTO … ON DUPLICATE KEY UPDATE
但是你以为这样就完美的解决了吗? 马上就会带来另外一场灾难,请看下面的错误场景
错误场景二
ERROR 1062 (23000): Duplicate entry 'x' for key 'PRIMARY'
- step1 初始化记录
mater:lc> REPLACE INTO test (col_1,col_2) values('a','a');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master:lc> REPLACE INTO test (col_1,col_2) values('b','b');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master:lc> REPLACE INTO test (col_1,col_2) values('c','c');
Query OK, 1 row affected (0.00 sec) --注意,这里是影响了1条记录
master > show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
slave > show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
可以看到:
(1)写入三条记录之后,test表的 AUTO_INCREMENT 增长为 4,也就是说下一条不手工为 id 指定值的记录,id 字段的值会是 4;
(2)Master库和Slave库的AUTO_INCREMENT 均增长到4,是一致的:因为初始化记录的该部分replace into操作等效于insert into操作,没有key冲突
- step2 构造错误场景
* master
mater:lc> REPLACE INTO test (col_1,col_2) values('c','cc');
Query OK, 2 rows affected (0.00 sec) --注意,这里是影响了两条记录
mater:lc> show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
master:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | a | a |
| 2 | b | b |
| 4 | c | cc |
+----+-------+-------+
3 rows in set (0.00 sec)
* slave
slave:lc> show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
slave:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | a | a |
| 2 | b | b |
| 4 | c | cc |
+----+-------+-------+
3 rows in set (0.00 sec)
有key冲突的replace into操作后,即相当于delete+insert,这时候 slave 上出现了诡异的问题:可以知道,当前表内数据 id 字段的最大值是 4,AUTO_INCREMENT 应该为 5,但在 slave 上 AUTO_INCREMENT 却并未更新,这会有什么问题呢?
接着往下看
- step3 错误案例产生
* 假设有一天,master 挂了, 由slave 提升为 new mater
原slave:lc> show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
原slave:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | a | a |
| 2 | b | b |
| 4 | c | cc |
+----+-------+-------+
3 rows in set (0.00 sec)
===注意==
root:lc> REPLACE INTO test (col_1,col_2) values('d','d');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
- 把这个 slave 提升为 master 之后,由于 AUTO_INCREMENT 比实际的 next id 还要小,写入新记录时就会发生 duplicate key error,每次冲突之后 AUTO_INCREMENT += 1,直到增长为 max(id) + 1 之后才能恢复正常。
- 总结
* Row 模式,主从情况下,replace into 和 INSERT INTO … ON DUPLICATE KEY UPDATE 都会导致以上问题的发生
* 解决方案: 最后可以通过alter table auto_increment值解决,但是这样已经造成mater的表很长时间没有写入了。。。
最后总结
- replace with unique key
1. 禁止 replace into (错误一,错误二 都会发生)
2. 禁止 INSERT INTO … ON DUPLICATE KEY UPDATE (错误二 会发生)
- replace with primary key
1. 禁止 replace into (会发生错误场景一的案例,丢失部分字段数据)
2. 可以使用INSERT INTO … ON DUPLICATE KEY UPDATE 代替 replace into
参考资料:
MySQL "replace into" 的坑 : https://www.cnblogs.com/monian/archive/2014/10/09/4013784.html
MySQL 小心使用 replace into : https://yq.aliyun.com/articles/57855