【轉】MySQL的Replace into 與Insert into on duplicate key update真正的不同之處

標籤：

原文連結：http://www.jb51.net/article/47090.htm 今天聽同事介紹oracle到mysql的資料migration，他用了Insert into ..... on duplicate key update ...，我當時就想怎麼不用Replace呢，於是回來就仔細查了下，它們果然還是有區別的  看下面的例子吧： 
1 Replace into ... 1.1 錄入未經處理資料 mysql> use test; Database changed mysql> 

mysql> CREATE TABLE t1 SELECT 1 AS a, ‘c3‘ AS b, ‘c2‘ AS c; ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_INCREMENT ; Query OK, 1 row affected (0.03 sec) Records: 1  Duplicates: 0  Warnings: 0

 

mysql> INSERT INTO t1 SELECT 2,‘2‘, ‘3‘; Query OK, 1 row affected (0.01 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into t1(b,c) select ‘r2‘,‘r3‘; Query OK, 1 row affected (0.08 sec) Records: 1  Duplicates: 0  Warnings: 0

 

1.2 開始replace操作 mysql> REPLACE INTO t1(a,b) VALUES(2,‘a‘) ; Query OK, 2 rows affected (0.06 sec)

【】看到這裡，replace，看到這裡，a=2的記錄中c欄位是空串了， 所以當與key衝突時，replace覆蓋相關欄位，其它欄位填充預設值，可以理解為重複資料刪除key的記錄，新插入一條記錄，一個delete原有記錄再insert的操作。

1.3 但是不知道對主鍵的auto_increment有無影響，接下來測試一下：

mysql> insert into t1(b,c) select ‘r4‘,‘r5‘; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c3 | c2 | | 2 | a | | | 3 | r2 | r3 | | 5 | r4 | r5 | +---+----+----+ 4 rows in set (0.00 sec)

 

 

【】從這裡可以看出，新的自增不是從4開始，而是從5開始，就表示一個repalce操作，主鍵中的auto_increment會累加1. 所以總結如下： Replace:

當沒有key時，replace相當於普通的insert. 當有key時，可以理解為重複資料刪除key的記錄，在保持key不變的情況下，delete原有記錄，再insert新的記錄，新紀錄的值只會錄入replace語句中欄位的值，其餘沒有在replace語句中的欄位，會自動填滿預設值。

2.1 ok，再來看Insert into ..... on duplicate key update，

mysql> insert into t1(a,b) select ‘3‘,‘r5‘ on duplicate key update b=‘r5‘; Query OK, 2 rows affected, 1 warning (0.19 sec) Records: 1 Duplicates: 1 Warnings: 1 mysql> select * from t1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c3 | c2 | | 2 | a | | | 3 | r5 | r3 | | 5 | r4 | r5 | +---+----+----+ 4 rows in set (0.00 sec)

【】a=5時候，原來的c值還在，這表示當key有時，只執行後面的udate動作陳述式.

2.2 再檢查auto_increment情況。

mysql> insert into t1(a,b) select ‘3‘,‘r5‘ on duplicate key update b=‘r5‘; Query OK, 2 rows affected, 1 warning (0.19 sec) Records: 1 Duplicates: 1 Warnings: 1 mysql> select * from t1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c3 | c2 | | 2 | a | | | 3 | r5 | r3 | | 5 | r4 | r5 | +---+----+----+ 4 rows in set (0.00 sec) mysql> insert into t1(b,c) select ‘r6‘,‘r7‘; Query OK, 1 row affected (0.19 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+----+----+ | a | b | c | +---+----+----+ | 1 | c3 | c2 | | 2 | a | | | 3 | r5 | r3 | | 5 | r4 | r5 | | 7 | r6 | r7 | +---+----+----+ 5 rows in set (0.00 sec) 

【】從這裡可以看出，新的自增不是從6開始，而是從7開始，就表示一個Insert .. on deplicate udate操作，主鍵中的auto_increment也跟replace一樣累加1.

2.3 再看下當沒有key的時候，insert .. on deplicate update的情況

mysql> insert into t1(a,b,c) select ‘33‘,‘r5‘,‘c3‘ on duplicate key update b=‘r5‘; Query OK, 1 row affected, 1 warning (0.23 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> select * from t1; +----+----+----+ | a | b | c | +----+----+----+ | 1 | c3 | c2 | | 2 | a | | | 3 | b5 | r3 | | 5 | r4 | r5 | | 7 | r6 | r7 | | 9 | s6 | s7 | | 33 | r5 | c3 | +----+----+----+ 7 rows in set (0.00 sec)

看a=33的記錄，ok，全部錄入了。

3 總結從上面的測試結果看出，相同之處： （1），沒有key的時候，replace與insert .. on deplicate udpate相同。 （2），有key的時候，都保留主索引值，並且auto_increment自動+1 不同之處：有key的時候，replace是delete老記錄，而錄入新的記錄，所以原有的所有記錄會被清除，這個時候，如果replace語句的欄位不全的話，有些原有的比如例子中c欄位的值會被自動填滿為預設值。       而insert .. deplicate update則只執行update標記之後的sql，從表象上來看相當於一個簡單的update語句。       但是實際上，根據我推測，如果是簡單的update語句，auto_increment不會+1，應該也是先delete，再insert的操作，只是在insert的過程中保留除update後面欄位以外的所有欄位的值。
 所以兩者的區別只有一個，insert .. on deplicate udpate保留了所有欄位的舊值，再覆蓋然後一起insert進去，而replace沒有保留舊值，直接刪除再insert新值。  從底層執行效率上來講，replace要比insert .. on deplicate update效率要高，但是在寫replace的時候，欄位要寫全，防止老的欄位資料被刪除。

【轉】MySQL的Replace into 與Insert into on duplicate key update真正的不同之處