MySQL replace into 語(yǔ)句淺析（一）
更新時(shí)間：2015年05月29日 10:12:46 投稿：junjie
這篇文章主要介紹了MySQL replace into 語(yǔ)句淺析（一）,本文講解了replace into的原理、使用方法及使用的場(chǎng)景和使用示例,需要的朋友可以參考下
一 介紹
在筆者支持業(yè)務(wù)過(guò)程中，經(jīng)常遇到開(kāi)發(fā)咨詢r(jià)eplace into 的使用場(chǎng)景以及注意事項(xiàng)，這里做個(gè)總結(jié)。從功能原理，性能和注意事項(xiàng)上做個(gè)說(shuō)明。
二 原理
2.1 當(dāng)表中存在主鍵但是不存在唯一建的時(shí)候。
表結(jié)構(gòu)
復(fù)制代碼 代碼如下:
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)
如果本來(lái)已經(jīng)存在的主鍵值，那么MySQL做update操作。
復(fù)制代碼 代碼如下:
### 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 */
如果本來(lái)相應(yīng)的主鍵值沒(méi)有，那么做insert 操作 replace into yy values(2,'bbb');
復(fù)制代碼 代碼如下:
### 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 當(dāng)表中主鍵和唯一鍵同時(shí)存在時(shí)
復(fù)制代碼 代碼如下:
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 主鍵沖突
復(fù)制代碼 代碼如下:
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í)，數(shù)據(jù)庫(kù)對(duì)表做先刪除然后插入的操作，也即先刪除id=1的記錄，然后插入新的id=1 的記錄(1,2,5).
復(fù)制代碼 代碼如下:
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 唯一建沖突
復(fù)制代碼 代碼如下:
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í)，數(shù)據(jù)庫(kù)對(duì)表 唯一鍵為(3，6)的行做update操作，將主鍵修改為要插入的值，id=4 改為id=5。
復(fù)制代碼 代碼如下:
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 主鍵和唯一鍵同時(shí)沖突,如果需要插入的值的主鍵 和唯一和表中已經(jīng)存在的存在沖突。
復(fù)制代碼 代碼如下:
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 處理的時(shí)候 ，先刪除id=1的行，然后更新了id=5的行。
復(fù)制代碼 代碼如下:
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é)論
對(duì)表進(jìn)行replace into操作的時(shí)候，
當(dāng)不存在沖突時(shí),replace into 相當(dāng)于insert操作。
當(dāng)存在pk沖突的時(shí)候是先delete再insert,如果主鍵是自增的，則自增主鍵會(huì)做 +1 操作?！?.5,5.6版本均做過(guò)測(cè)試】
當(dāng)存在uk沖突的時(shí)候是直接update。,如果主鍵是自增的，則自增主鍵會(huì)做 +1 操作。 【5.5,5.6版本均做過(guò)測(cè)試】
了解上述原理和結(jié)論之后，以后再遇到replace into 的時(shí)候，相信各位讀者可以知道如何選擇，由于篇幅限制，后續(xù)文章會(huì)基于replace into原理，講述生產(chǎn)過(guò)程中的注意事項(xiàng)。
相關(guān)文章
MySQL表的創(chuàng)建及字段介紹(小白入門(mén)篇)
這篇文章主要為大家介紹了MySQL表的創(chuàng)建及字段介紹(小白入門(mén)篇)，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-05-05
兩種方法實(shí)現(xiàn)mysql分組計(jì)數(shù)，范圍匯總
這篇文章主要介紹了兩種方法實(shí)現(xiàn)mysql分組計(jì)數(shù)，范圍匯總，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
MySQL?8.0新功能監(jiān)控統(tǒng)計(jì)限制連接不再擔(dān)心被垃圾SQL搞爆內(nèi)存
這篇文章主要介紹了MySQL?8.0新功能監(jiān)控統(tǒng)計(jì)限制連接不再擔(dān)心被垃圾SQL搞爆內(nèi)存詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-05-05
ICP(Index Condition Pushdown)是在MySQL 5.6版本上推出的查詢優(yōu)化策略,把本來(lái)由Server層做的索引條件檢查下推給存儲(chǔ)引擎層來(lái)做,下面這篇文章主要給大家介紹了關(guān)于MySQL索引下推的相關(guān)資料,需要的朋友可以參考下2021-09-09
mysql中迅速插入百萬(wàn)條測(cè)試數(shù)據(jù)的方法
最近想到創(chuàng)建一個(gè)大量數(shù)據(jù)的測(cè)試環(huán)境，于是找了一下怎么插入100W條數(shù)據(jù)，我用的是20個(gè)字段2012-04-04
這篇文章主要詳細(xì)介紹了mysql表的清空、刪除和修改操作的方法，以及一些常用的mysql的常用操作，非常的簡(jiǎn)單實(shí)用，有需要的可以參考下2014-09-09
深入探尋mysql自增列導(dǎo)致主鍵重復(fù)問(wèn)題的原因
前幾天開(kāi)發(fā)的同事反饋一個(gè)利用load data infile命令導(dǎo)入數(shù)據(jù)主鍵沖突的問(wèn)題，分析后確定這個(gè)問(wèn)題可能是mysql的一個(gè)bug，這里提出來(lái)給大家分享下。以免以后有童鞋遇到類似問(wèn)題百思不得其解，難以入眠，哈哈。2014-08-08
MySQL timestamp自動(dòng)更新時(shí)間分享
在mysql中timestamp數(shù)據(jù)類型是一個(gè)比較特殊的數(shù)據(jù)類型，他可以自動(dòng)在你不使用程序更新情況下只要你更新了記錄timestamp會(huì)自動(dòng)更新時(shí)間2013-06-06
要操作表首先需要選定數(shù)據(jù)庫(kù)，因?yàn)楸硎谴嬖谟跀?shù)據(jù)庫(kù)內(nèi)的;表的基本操作包括：創(chuàng)建表、顯示表、查看表基本結(jié)構(gòu)、查看表詳細(xì)結(jié)構(gòu)以及刪除表等等，需要了解的朋友可以參考下2013-01-01
MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
對(duì)多的兩張表，一般是一張表的外鍵關(guān)聯(lián)到另一個(gè)表的主鍵，接下來(lái)為大家介紹下用子查詢代替非主鍵連接查詢，感興趣的朋友可以參考下哈，希望對(duì)你有所幫助2013-04-04