MySQL replace into的使用細則

在Oracle中有merge into的語法，可以達到一個語句完成同時修改，新增資料的功能，MySQL裡面沒有merge into的語法，卻有replace into。
我們來看看replace into的使用細則。
為了方便演示，我首先建立一個表 users
create table users(
user_id int(11) unsigned not null,
user_name varchar(64) default null,
primary key(user_id)
)engine=innodb default charset=UTF8;
插入2行資料，可能搞Oracle的同學就不適應了，SQL怎麼能這麼寫，不過用起來確實蠻有意思。
> insert into users (user_id,user_name) values(1,'aa'),(2,'bb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
資料情況如下：    
> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | aa        |
|       2 | bb        |
+---------+-----------+
2 rows in set (0.00 sec)
好了，我們來看看replace into的使用，如果向表裡插入資料，表裡已經存在同樣的資料，replace into是會直接更新還是會刪除，然後插入。
要搞明白這一點很重要，因為這個直接會影響到資料的準確性。
我們先看看replace into的使用。比如插入下面的一條記錄。
> replace into users(user_id, user_name) values(1, 'cc');
Query OK, 2 rows affected (0.00 sec)
完成之後資料的情況如下：
> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | cc        |
|       2 | bb        |
+---------+-----------+
2 rows in set (0.00 sec)
看來資料像是被替換了，又好像是刪除後，重新覆蓋的。怎麼驗證呢。
我們可以先試試trace的方法。是否能夠有所收穫。
首先用explain extended的方式，這種方式會得到很多執行計劃的細節資訊。

根據輸出來看，這種方式得不到預期的資料結果。
我們換一個方式，在5.6以上版本使用optimizer_trace
> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

> replace into users(user_id, user_name) values(1, 'dd');
Query OK, 2 rows affected (0.01 sec)
輸出結果如下，還是沒有得到很詳細的資訊。

這個時候不要氣餒，要知道辦法總比困難多。我們可以換一個新的思路來測試，而且還能順帶驗證，何樂而不為。
我們重新建立一個表users2,和users的唯一不同在於user_id使用了auto_increment的方式。
CREATE TABLE `users2` (
user_id int(11) unsigned not null AUTO_INCREMENT,
user_name varchar(64) default null,
primary key(user_id)
)engine=innodb default charset=UTF8;

插入3行資料。
> INSERT INTO users2 (user_id,user_name) VALUES (1, 'aa'), (2, 'bb'), (3, 'cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
這個時候檢視建表的DDL如下：
> SHOW CREATE TABLE users2\G
*************************** 1. row ***************************
       Table: users2
Create Table: CREATE TABLE `users2` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
資料情況如下：
> SELECT * FROM users2 ;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | aa        |
|       2 | bb        |
|       3 | cc        |
+---------+-----------+
3 rows in set (0.00 sec)
我們先做一個replace into的操作。
> REPLACE INTO users2 (user_id,user_name) VALUES (1, 'dd');
Query OK, 2 rows affected (0.00 sec)

資料情況如下，原來user_id為1的資料做了變更。
> SELECT * FROM users2;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | dd        |
|       2 | bb        |
|       3 | cc        |
+---------+-----------+
3 rows in set (0.01 sec)
再次檢視auto_increment的值還是4
> SHOW CREATE TABLE users2\G
*************************** 1. row ***************************
       Table: users2
Create Table: CREATE TABLE `users2` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
這個時候還是很難得出一個結論，切記不要想當然。replace into需要表中存在主鍵或者唯一性索引，user_id存在主鍵，我們給user_name建立一個唯一性索引。
> alter table users2 add unique key users2_uq_name(user_name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
好了，重要的時刻到了，我們看看下面的語句的效果。只在語句中提及user_name,看看user_id是遞增還是保留當前的值。
> REPLACE INTO users2 (user_name) VALUES ('dd');
Query OK, 2 rows affected (0.00 sec)
可以看到user_id做了遞增，也就意味著這是一個全新的insert插入資料。
> select * from users2;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       2 | bb        |
|       3 | cc        |
|       4 | dd        |
+---------+-----------+
3 rows in set (0.00 sec)
這個時候再次檢視建表的DDL如下,auto_increment確實是遞增了。
CREATE TABLE `users2` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `users2_uq_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
所以通過上面的測試和推理我們知道，replace into是delete,insert的操作，而非基於當前資料的update。
如此一來我們使用replace into的時候就需要格外注意，可能有些操作非我們所願，如果插入資料時存在重複的資料，是更新當前記錄的情況，該怎麼辦呢，可以使用replace into的姊妹篇語句，insert into on duplicate key 的方式，後面需要使用update選項。
比如我們還是基於上面的資料，插入user_name為'dd'的資料，如果存在則修改。
> INSERT INTO users2 (user_name) VALUES ('dd') ON DUPLICATE KEY UPDATE user_name=VALUES(user_name);
Query OK, 0 rows affected (0.00 sec)
根據執行結果來看，沒有修改資料，比我們期望的還要好一些。
所以任何語句和功能都不是萬能的，還得看場景，脫離了使用場景就很難說得清了。
此外，補充replace into的另外一種使用方式，供參考。
> replace into users2(user_id,user_name) select 2,'bbbb'  ;
Query OK, 2 rows affected (0.01 sec)
Records: 1  Duplicates: 1  Warnings: 0

> select *from users2;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       2 | bbbb      |
|       3 | cc        |
|       4 | dd        |
+---------+-----------+
3 rows in set (0.00 sec)
其實再次檢視replace into的使用，發現日誌中已經赫然提醒，2 rows affected.當然我們有過程有結論，也算是一種不錯的嘗試了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2126083/，如需轉載，請註明出處，否則將追究法律責任。