摘要： MySQL replace into 錯(cuò)誤案例 背景 * MySQL5.7 * ROW模式 * 表結(jié)構(gòu) CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `col_1` varc

MySQL replace into 錯(cuò)誤案例

背景

* MySQL5.7  * ROW模式   * 表結(jié)構(gòu) 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

錯(cuò)誤場(chǎng)景一

其他字段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 構(gòu)造錯(cuò)誤場(chǎng)景
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)
  • 總結(jié)
  1. col_3 的值，從原來(lái)的c，變成了NULL，天吶，數(shù)據(jù)不見了。 id 也變了。
  2. 用戶原本的需求，應(yīng)該是如果col_1='c' 存在，那么就改變col_2='cc'，其余的記錄保持不變，結(jié)果id,col_3都變化了
  3. 解決方案就是：將replace into 改成 INSERT INTO … ON DUPLICATE KEY UPDATE

但是你以為這樣就完美的解決了嗎？ 馬上就會(huì)帶來(lái)另外一場(chǎng)災(zāi)難，請(qǐng)看下面的錯(cuò)誤場(chǎng)景

錯(cuò)誤場(chǎng)景二

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 |
  • step2 構(gòu)造錯(cuò)誤場(chǎng)景
* 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)
  • step3 錯(cuò)誤案例產(chǎn)生
* 假設(shè)有一天，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'
  • 總結(jié)
* Row 模式，主從情況下，replace into 和 INSERT INTO … ON DUPLICATE KEY UPDATE 都會(huì)導(dǎo)致以上問題的發(fā)生 * 解決方案： 最后可以通過alter table auto_increment值解決，但是這樣已經(jīng)造成mater的表很長(zhǎng)時(shí)間沒有寫入了。。。

最后總結(jié)

  • replace with unique key
1. 禁止 replace into (錯(cuò)誤一，錯(cuò)誤二 都會(huì)發(fā)生) 2. 禁止 INSERT INTO … ON DUPLICATE KEY UPDATE （錯(cuò)誤二 會(huì)發(fā)生）
  • replace with primary key
1. 禁止 replace into (會(huì)發(fā)生錯(cuò)誤場(chǎng)景一的案例，丟失部分字段數(shù)據(jù)) 2. 可以使用INSERT INTO … ON DUPLICATE KEY UPDATE 代替 replace into