MySQL小心使用replaceinto

蘭春發表於2016-07-20
MySql

MySQL 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

錯誤場景一

其他欄位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)
  • 總結
  1. col_3 的值，從原來的c，變成了NULL，天吶，資料不見了。 id 也變了。
  2. 使用者原本的需求，應該是如果col_1=`c` 存在，那麼就改變col_2=`cc`，其餘的記錄保持不變，結果id,col_3都變化了
  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 |
  • 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)
  • 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`
  • 總結
* 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


相關文章