MySQL中replace into怎么用

這篇文章給大家分享的是有關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.當然我們有過程有結論，也算是一種不錯的嘗試了。

感謝各位的閱讀！關于“MySQL中replace into怎么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！