MySQL replace into 語(yǔ)句淺析（一）_MySQL教程
推薦：MySQL定期自動(dòng)刪除表單位8億多條的日志表，經(jīng)過(guò)自動(dòng)分表之后，需要自動(dòng)刪除30天前創(chuàng)建的日志表。 但是只是在Master下線(xiàn)這些日志表，而Slave還需要保持在線(xiàn)，以備查詢(xún)。 由于Master-Slave結(jié)構(gòu)，在Drop表之前，設(shè)置@@session.sql_log_bin=0,那么Drop的行為就沒(méi)有記錄到binlog，所以Slave的日
這篇文章主要介紹了MySQL replace into 語(yǔ)句淺析(一),本文講解了replace into的原理、使用方法及使用的場(chǎng)景和使用示例,需要的朋友可以參考下
一 介紹
在筆者支持業(yè)務(wù)過(guò)程中，經(jīng)常遇到開(kāi)發(fā)咨詢(xún)r(jià)eplace into 的使用場(chǎng)景以及注意事項(xiàng)，這里做個(gè)總結(jié)。從功能原理，性能和注意事項(xiàng)上做個(gè)說(shuō)明。
二 原理
2.1 當(dāng)表中存在主鍵但是不存在唯一建的時(shí)候。
表結(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)
如果本來(lái)已經(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 */
如果本來(lái)相應(yīng)的主鍵值沒(méi)有，那么做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 當(dāng)表中主鍵和唯一鍵同時(shí)存在時(shí)
代碼如下:
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í)，數(shù)據(jù)庫(kù)對(duì)表做先刪除然后插入的操作，也即先刪除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í)，數(shù)據(jù)庫(kù)對(duì)表 唯一鍵為(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 主鍵和唯一鍵同時(shí)沖突,如果需要插入的值的主鍵 和唯一和表中已經(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 處理的時(shí)候 ，先刪除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é)論
對(duì)表進(jìn)行replace into操作的時(shí)候，
當(dāng)不存在沖突時(shí),replace into 相當(dāng)于insert操作。
當(dāng)存在pk沖突的時(shí)候是先delete再insert,如果主鍵是自增的，則自增主鍵會(huì)做 +1 操作�！�5.5,5.6版本均做過(guò)測(cè)試】
分享：MySQL中的CONCAT函數(shù)使用教程這篇文章主要介紹了MySQL中的CONCAT函數(shù)使用教程,是Python入門(mén)學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下 使用MySQL CONCAT()函數(shù)將兩個(gè)字符串連接起來(lái)，形成一個(gè)單一的字符串。試試下面的例子： ? 了解CONCAT函數(shù)更詳細(xì)，考慮EMPLOYEE_TBL表具有以下記錄： ? 可以用以下
- 防止服務(wù)器宕機(jī)時(shí)MySQL數(shù)據(jù)丟失的幾種方案
- MySQL Semisynchronous Replication介紹
- MySQL延遲關(guān)聯(lián)性能優(yōu)化方法
- MySQL 5.7增強(qiáng)版Semisync Replication性能優(yōu)化
- MySQL Index Condition Pushdown(ICP)性能優(yōu)化方法實(shí)例
- MySQL order by性能優(yōu)化方法實(shí)例
- MySQL slave_net_timeout參數(shù)解決的一個(gè)集群?jiǎn)栴}案例
- 使用innodb_force_recovery解決MySQL崩潰無(wú)法重啟問(wèn)題
- MySQL replace into 語(yǔ)句淺析（二）
- MySQL定期自動(dòng)刪除表
- MySQL中的CONCAT函數(shù)使用教程
- MySQL中的RAND()函數(shù)使用詳解
MySQL教程Rss訂閱編程教程搜索
MySQL教程推薦
- 基于mysql查詢(xún)語(yǔ)句的使用詳解
- 如何通過(guò)SQL找出2個(gè)表里值不同的列的方法
- MySQL為什么會(huì)索引失效？
- 如何用workbench導(dǎo)出mysql數(shù)據(jù)庫(kù)關(guān)系圖
- sql語(yǔ)句中刪除表數(shù)據(jù)drop、truncate和delete的用法
- 綠色版mysql注冊(cè)卸載服務(wù)方法
- MySQL筆記之字符串函數(shù)的應(yīng)用
- 解決MYSQL導(dǎo)入腳本數(shù)據(jù)中文亂碼
- 安裝SQL server 提示重新啟動(dòng)計(jì)算機(jī)失敗怎么解決？
- mysql錯(cuò)誤及解決全攻略
猜你也喜歡看這些
- 揭秘一個(gè)修改Oracle數(shù)據(jù)庫(kù)用戶(hù)密碼的訣竅
- SQL Server備份文件中導(dǎo)入現(xiàn)存數(shù)據(jù)庫(kù)
- SQL查詢(xún)語(yǔ)句精華使用簡(jiǎn)要
- 怎樣從數(shù)據(jù)行入手保護(hù)SQL Server數(shù)據(jù)安全
- 解讀SQL Server游標(biāo)使用實(shí)例
- 讓你的MySQL數(shù)據(jù)庫(kù)徹底與中文聯(lián)姻
- 淺談選擇SQL Server恢復(fù)模型確保正確備份
- SQL Server各種日期計(jì)算方法之二
- 怎樣使用SQLServer數(shù)據(jù)庫(kù)查詢(xún)累計(jì)值
- SQL Server 2005 DTS導(dǎo)入平面數(shù)據(jù)出現(xiàn)錯(cuò)誤解決方案
- 相關(guān)鏈接：
- 教程說(shuō)明：
MySQL教程-MySQL replace into 語(yǔ)句淺析（一）
。