MySQL replace into 語句淺析（一）
更新時間：2015年05月29日 10:12:46 投稿：junjie
這篇文章主要介紹了MySQL replace into 語句淺析（一）,本文講解了replace into的原理、使用方法及使用的場景和使用示例,需要的朋友可以參考下
一 介紹
在筆者支持業(yè)務(wù)過程中，經(jīng)常遇到開發(fā)咨詢replace into 的使用場景以及注意事項，這里做個總結(jié)。從功能原理，性能和注意事項上做個說明。
二 原理
2.1 當表中存在主鍵但是不存在唯一建的時候。
表結(jié)構(gòu)
復制代碼 代碼如下:
CREATE TABLE `yy` (
`id` bigint(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
root@test 02:43:58>insert into yy values(1,'abc');
Query OK, 1 row affected (0.00 sec)
root@test 02:44:25>replace into yy values(2,'bbb');
Query OK, 1 row affected (0.00 sec)
root@test 02:55:42>select * from yy;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | bbb |
+----+------+
2 rows in set (0.00 sec)
root@test 02:55:56>replace into yy values(1,'ccc');
Query OK, 2 rows affected (0.00 sec)
如果本來已經(jīng)存在的主鍵值，那么MySQL做update操作。
復制代碼 代碼如下:
### UPDATE test.yy
### WHERE
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
如果本來相應(yīng)的主鍵值沒有，那么做insert 操作 replace into yy values(2,'bbb');
復制代碼 代碼如下:
### INSERT INTO test.yy
### SET
### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 623
#140314 2:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569
2.2 當表中主鍵和唯一鍵同時存在時
復制代碼 代碼如下:
CREATE TABLE `yy` (
`id` int(11) NOT NULL DEFAULT \'0\',
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
PRIMARY KEY (`a`),
UNIQUE KEY `uk_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
情形1 主鍵沖突
復制代碼 代碼如下:
root@test 04:37:18>replace into yy values(1,2,3);
Query OK, 1 row affected (0.00 sec)
root@test 04:37:37>replace into yy values(2,2,4);
Query OK, 1 row affected (0.00 sec)
root@test 04:38:05>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 2 | 4 |
+----+------+------+
2 rows in set (0.00 sec)
root@test 04:38:50>replace into yy values(1,2,5);
Query OK, 2 rows affected (0.00 sec)
root@test 04:38:58>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 4 |
| 1 | 2 | 5 |
+----+------+------+
2 rows in set (0.00 sec)
主鍵沖突時，數(shù)據(jù)庫對表做先刪除然后插入的操作，也即先刪除id=1的記錄，然后插入新的id=1 的記錄(1,2,5).
復制代碼 代碼如下:
BINLOG '
Io5hVROWYHC+KwAAAEICAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
Io5hVRmWYHC+KgAAAGwCAAAAAMoMAAAAAAAAA//4AQAAAAIAAAADAAAA
### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=3 /* INT meta=0 nullable=1 is_null=0 */
Io5hVReWYHC+KgAAAJYCAAAAAMoMAAAAAAEAA//4AQAAAAIAAAAFAAAA
'/*!*/;
### INSERT INTO test.yy
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=5 /* INT meta=0 nullable=1 is_null=0 */
# at 662
#150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
COMMIT/*!*/
情形2 唯一建沖突
復制代碼 代碼如下:
root@test 04:48:30>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 4 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)
root@test 04:53:21>replace into yy values(5,3,6);
Query OK, 2 rows affected (0.00 sec)
root@test 04:53:40>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 5 | 3 | 6 |
+----+------+------+
4 rows in set (0.00 sec)
主鍵不沖突，唯一鍵沖突時，數(shù)據(jù)庫對表 唯一鍵為(3，6)的行做update操作，將主鍵修改為要插入的值，id=4 改為id=5。
復制代碼 代碼如下:
BINLOG \'
lJFhVROWYHC+KwAAANoAAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
lJFhVRiWYHC+OAAAABIBAAAAAMoMAAAAAAEAA///+AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yy
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
# at 274
#150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
COMMIT/*!*/
情形3 主鍵和唯一鍵同時沖突,如果需要插入的值的主鍵 和唯一和表中已經(jīng)存在的存在沖突。
復制代碼 代碼如下:
root@test 04:53:52>replace into yy values(1,3,6);
Query OK, 3 rows affected (0.00 sec) ---注意此處影響的行數(shù)是3
root@test 04:55:35>select * from yy;
+----+------+------+
| id | b | c |
+----+------+------+
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 1 | 3 | 6 |
+----+------+------+
3 rows in set (0.00 sec)
要插入的值(1,3,6) 主鍵于 表里面的id=1的值沖突，唯一鍵(3,6)和表中id=5的記錄沖突，MySQL 處理的時候 ，先刪除id=1的行，然后更新了id=5的行。
復制代碼 代碼如下:
BINLOG \'
B5JhVROWYHC+KwAAAJwBAAAAAMoMAAAAAAEABHRlc3QAAnl5AAMDAwMABg==
B5JhVRmWYHC+KgAAAMYBAAAAAMoMAAAAAAAAA//4AQAAAAIAAAAEAAAA
### DELETE FROM test.yy
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=2 /* INT meta=0 nullable=1 is_null=0 */
### @3=4 /* INT meta=0 nullable=1 is_null=0 */
B5JhVRiWYHC+OAAAAP4BAAAAAMoMAAAAAAEAA///+AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA=
\'/*!*/;
### UPDATE test.yy
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=3 /* INT meta=0 nullable=1 is_null=0 */
### @3=6 /* INT meta=0 nullable=1 is_null=0 */
# at 510
#150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
COMMIT/*!*/
三 結(jié)論
對表進行replace into操作的時候，
當不存在沖突時,replace into 相當于insert操作。
當存在pk沖突的時候是先delete再insert,如果主鍵是自增的，則自增主鍵會做 +1 操作?！?.5,5.6版本均做過測試】
當存在uk沖突的時候是直接update。,如果主鍵是自增的，則自增主鍵會做 +1 操作。 【5.5,5.6版本均做過測試】
了解上述原理和結(jié)論之后，以后再遇到replace into 的時候，相信各位讀者可以知道如何選擇，由于篇幅限制，后續(xù)文章會基于replace into原理，講述生產(chǎn)過程中的注意事項。
相關(guān)文章
CentOS 6.5下yum安裝 MySQL-5.5全過程圖文教程
在linux安裝mysql是一個困難的事情，yum安裝一般是安裝的mysql5.1，現(xiàn)在經(jīng)過自己不懈努力終于能用yum安裝mysql5.5了。下面通過兩種方法給大家介紹CentOS 6.5下yum安裝 MySQL-5.5全過程，一起學習吧2016-05-05
這篇文章主要介紹了mysql啟動服務(wù)報1058錯誤的解決方法,需要的朋友可以參考下2014-03-03
mysql數(shù)據(jù)庫常見基本操作實例分析【創(chuàng)建、查看、修改及刪除數(shù)據(jù)庫】
這篇文章主要介紹了mysql數(shù)據(jù)庫常見基本操作,結(jié)合實例形式分析了mysql創(chuàng)建、查看、修改及刪除數(shù)據(jù)庫實現(xiàn)方法與操作注意事項,需要的朋友可以參考下2020-04-04
這篇文章主要介紹了MySQL 的基礎(chǔ)常用命令，在執(zhí)行語句的時候，很多命令都是必須記住的，想具體了解的小伙伴請參考下面文章內(nèi)容2021-09-09
解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題
這篇文章主要介紹了解決數(shù)據(jù)庫有數(shù)據(jù)但查詢出來的值為Null問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
ONLY_FULL_GROUP_BY是MySQL中一個重要的SQL模式,確保在使用GROUP BY時,所有非聚合函數(shù)列必須在GROUP BY子句中出現(xiàn),避免數(shù)據(jù)歧義和不確定性,下面就來介紹一下具體使用2024-09-09
本文主要介紹了MySQL索引失效的幾種情況小結(jié)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-03-03
insert into … on duplicate key update / replace into 多行數(shù)據(jù)介紹
當我插入一條數(shù)據(jù)時，我要判斷(k1,k2)是否已經(jīng)存在（1條selete）,若存在就update，不存在就insert2013-08-08
MySQL循環(huán)語句之while循環(huán)測試
MySQL有循環(huán)語句操作，while 循環(huán)、loop循環(huán)和repeat循環(huán)，目前我只測試了 while 循環(huán)，下面與大家分享下2014-07-07
這篇文章主要介紹了MySQL統(tǒng)計庫表大小的相關(guān)資料，文中示例代碼非常詳細，幫助大家更好的理解和學習，感興趣的朋友可以了解下2020-07-07