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 開(kāi)始replace操作
mysql> REPLACE INTO t1(a,b) VALUES(2,'a') ;
Query OK, 2 rows affected (0.06 sec)

【】看到這里，replace，看到這里，a=2的記錄中c字段是空串了，
所以當(dāng)與key沖突時(shí)，replace覆蓋相關(guān)字段，其它字段填充默認(rèn)值，可以理解為刪除重復(fù)key的記錄，新插入一條記錄，一個(gè)delete原有記錄再insert的操作。

1.3 但是不知道對(duì)主鍵的auto_increment有無(wú)影響，接下來(lái)測(cè)試一下：

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開(kāi)始，而是從5開(kāi)始，就表示一個(gè)repalce操作，主鍵中的auto_increment會(huì)累加1.
所以總結(jié)如下：
Replace:

當(dāng)沒(méi)有key時(shí)，replace相當(dāng)于普通的insert.
當(dāng)有key時(shí)，可以理解為刪除重復(fù)key的記錄，在保持key不變的情況下，delete原有記錄，再insert新的記錄，新紀(jì)錄的值只會(huì)錄入replace語(yǔ)句中字段的值，其余沒(méi)有在replace語(yǔ)句中的字段，會(huì)自動(dòng)填充默認(rèn)值。

2.1 ok，再來(lái)看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時(shí)候，原來(lái)的c值還在，這表示當(dāng)key有時(shí)，只執(zhí)行后面的udate操作語(yǔ)句.

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開(kāi)始，而是從7開(kāi)始，就表示一個(gè)Insert .. on deplicate udate操作，主鍵中的auto_increment也跟replace一樣累加1.

2.3 再看下當(dāng)沒(méi)有key的時(shí)候，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 總結(jié)從上面的測(cè)試結(jié)果看出，相同之處：
（1），沒(méi)有key的時(shí)候，replace與insert .. on deplicate udpate相同。
（2），有key的時(shí)候，都保留主鍵值，并且auto_increment自動(dòng)+1
不同之處：有key的時(shí)候，replace是delete老記錄，而錄入新的記錄，所以原有的所有記錄會(huì)被清除，這個(gè)時(shí)候，如果replace語(yǔ)句的字段不全的話，有些原有的比如例子中c字段的值會(huì)被自動(dòng)填充為默認(rèn)值。
      而insert .. deplicate update則只執(zhí)行update標(biāo)記之后的sql，從表象上來(lái)看相當(dāng)于一個(gè)簡(jiǎn)單的update語(yǔ)句。
      但是實(shí)際上，根據(jù)我推測(cè)，如果是簡(jiǎn)單的update語(yǔ)句，auto_increment不會(huì)+1，應(yīng)該也是先delete，再insert的操作，只是在insert的過(guò)程中保留除update后面字段以外的所有字段的值。

 所以兩者的區(qū)別只有一個(gè)，insert .. on deplicate udpate保留了所有字段的舊值，再覆蓋然后一起insert進(jìn)去，而replace沒(méi)有保留舊值，直接刪除再insert新值。
 從底層執(zhí)行效率上來(lái)講，replace要比insert .. on deplicate update效率要高，但是在寫replace的時(shí)候，字段要寫全，防止老的字段數(shù)據(jù)被刪除。

個(gè)人傾向與用Replace。

最新評(píng)論