MySQL replace into的使用方法詳解

replace into是數據存在就替換不存在就插入了我們這裡來看在MySQL replace into的使用方法詳解吧，具體的細節步驟如下文介紹．

在Oracle中有merge into的語法，可以達到一個語句完成同時修改，添加數據的功能，MySQL裡面沒有merge into的語法，卻有replace into。

REPLACE的運行與INSERT很相似。只有一點例外，假如表中的一個舊記錄與一個用於PRIMARY

KEY或一個UNIQUE索引的新記錄具有相同的值，則在新記錄被插入之前，舊記錄被刪除。
注意，除非表有一個PRIMARY KEY或UNIQUE索引，否則，使用一個REPLACE語句沒有意義。該

語句會與INSERT相同，因為沒有索引被用於確定是否新行復制了其它的行。

所有列的值均取自在REPLACE語句中被指定的值。所有缺失的列被設置為各自的默認值，這

和INSERT一樣。您不能從當前行中引用值，也不能在新行中使用值。如果您使用一個例如“

SET col_name = col_name + 1”的賦值，則對位於右側的列名稱的引用會被作為DEFAULT

(col_name)處理。因此，該賦值相當於SET col_name = DEFAULT(col_name) + 1。

為了能夠使用REPLACE，您必須同時擁有表的INSERT和DELETE權限。

REPLACE語句會返回一個數，來指示受影響的行的數目。該數是被刪除和被插入的行數的和

。如果對於一個單行REPLACE該數為1，則一行被插入，同時沒有行被刪除。如果該數大於1

，則在新行被插入前，有一個或多個舊行被刪除。如果表包含多個唯一索引，並且新行復制

了在不同的唯一索引中的不同舊行的值，則有可能是一個單一行替換了多個舊行。

受影響的行數可以容易地確定是否REPLACE只添加了一行，或者是否REPLACE也替換了其它行

：檢查該數是否為1（添加）或更大（替換）。

1. 嘗試把新行插入到表中

2. 當因為對於主鍵或唯一關鍵字出現重復關鍵字錯誤而造成插入失敗時：

a. 從表中刪除含有重復關鍵字值的沖突行

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

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},…),(…),…
或：

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
或：

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT …

REPLACE INTO `table` (`unique_column`,`num`) VALUES ('$unique_value',$num);跟INSERT INTO `table` (`unique_column`,`num`) VALUES('$unique_value',$num) ON DUPLICATE UPDATE num=$num;還是有些區別的.
區別就是replace into的時候會刪除老記錄。如果表中有一個自增的主鍵。
那麼就要出問題了。

首先，因為新紀錄與老記錄的主鍵值不同，所以其他表中所有與本表老數據主鍵id建立的關聯全部會被破壞。

其次，就是，頻繁的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.當然我們有過程有結論，也算是一種不錯的嘗試了。