MySQL REPLACE INTO 的使用-有解無憂

前段時間寫游戲合服工具時出現過一個問題，源DB和目標DB角色表中主鍵全部都不相同，從源DB取出玩家資料再使用 replace into 寫入目標DB中，結果總有幾條資料插入時會導致目標DB中原有的角色資料丟失了，仔細排查之后發現時replace into使用錯誤造成的，在這里總結下replace into的使用方式，可以幫助有幸看到這篇文章的朋友避開replace into 使用的坑，

replace into 執行流程

1. 嘗試向表中插入新行
2. 插入失敗，因為表中存在相同的主鍵或唯一索引

    a.洗掉表中所有相同的主線以及唯一索引的記錄

    b.再次嘗試向表中插入新行

與insert的區別

insert是直接插入記錄,如果表中存在相同的主鍵或唯一索引，插入失敗， replace into也是插入記錄，如果表中存在相同的主鍵或唯一索引，先洗掉相同主鍵或唯一索引記錄，再執行插入操作，如果表中不存在相同主鍵或唯一索引時，和insert時相同的，

注意

1. 使用replace into時需要對表有delete和insert的權限
2. replace into陳述句中所有缺失的欄位都會被設定為欄位的默認值
3. replace into執行記結果受影響的行數大于1行時，插入操作只有一行受影響，其他受影響的行是洗掉操作
4. replace..set col_name = col_name + 1時，col_name會被當做默認值,賦值最終等價于 col_name = DEFAULT(col_name) + 1 假如有如下的表：

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(32) NOT NULL,
  `uid` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)



mysql> replace into test(id,name,uid,age) values(1,'aa',101,20),(2,'bb',102,21);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0



mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  1 | aa   | 101 |   20 |
|  2 | bb   | 102 |   21 |
+----+------+-----+------+
2 rows in set (0.00 sec)

現在執行下面的操作：

mysql> replace into test(id,name,uid,age) values(2,'cc',103,22);      
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  1 | aa   | 101 |   20 |
|  2 | cc   | 103 |   22 |
+----+------+-----+------+
2 rows in set (0.00 sec)

replace into陳述句執行之后，2行受影響，主鍵id=2在表中已經存在，先洗掉表中主鍵，然后再插入新行資料

mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  1 | aa   | 101 |   20 |
|  2 | cc   | 103 |   22 |
+----+------+-----+------+
2 rows in set (0.00 sec)
mysql> replace into test(id,name,uid,age) values(2,'aa',100,25);
Query OK, 3 rows affected (0.02 sec)
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |   25 |
+----+------+-----+------+
1 row in set (0.00 sec)

replace into陳述句執行之后，3行受影響，主鍵id=2在表中已經存在，先洗掉表中主鍵行，唯一索引name='aa'再表中已經存在，先洗掉表中索引行，然后再插入新行資料

mysql> replace into test(id,name,uid,age) values(3,'dd',100,25);   
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |   25 |
|  3 | dd   | 100 |   25 |
+----+------+-----+------+
2 rows in set (0.01 sec)

replace into陳述句執行之后，1行受影響，表中沒有相同的主鍵以及唯一索引相同，uid欄位是普通索引，不是唯一索引，所以不會有洗掉操作，最終和insert陳述句效果一樣，插入一行新資料

mysql> replace into test set id = 3,name='dd',uid=100,age=age+1;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |   25 |
|  3 | dd   | 100 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)
mysql> select NULL + 1;
+----------+
| NULL + 1 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)
mysql> update test set age = age + 1 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |   26 |
|  3 | dd   | 100 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)

如果使用 replace...set col_name = col_name + 1 陳述句的話，col_name取的是默認值,這里age的默認值是NULL，set age = age + 1 等價于 set age = NULL + 1,結果還是為NULL 使用update...set col_name = col_name + 1就不會有這個問題，這里的主要原因是 replace 會先洗掉重復主鍵或唯一索引的記錄，再插入一行新資料，當洗掉原有資料之后 age 欄位就沒有值了，所以這里的 replace ...set age = age + 1，age 的最終值是NULL，我們修改下age的默認值，再執行replace...set age = age + 1看下結果

mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |   26 |
|  3 | dd   | 100 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)
mysql> alter table test alter age set default 3;   
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> replace into test set id = 2,name='aa',uid=100,age = age + 1;
Query OK, 2 rows affected (0.07 sec)
mysql> select * from test;
+----+------+-----+------+
| id | name | uid | age  |
+----+------+-----+------+
|  2 | aa   | 100 |    4 |
|  3 | dd   | 100 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)

把age的默認值修改成3之后，執行replace..set age = age + 1, age最終的值不在是NULL了，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62706.html

標籤：MySQL

上一篇：對"易購網上商城"專案開發的總結

下一篇：基礎架構（1）