當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

慎用MySQL replace语句

發布時間：2025/4/16 数据库 23 豆豆

生活随笔收集整理的這篇文章主要介紹了慎用MySQL replace语句小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

語法：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

REPLACE [LOW_PRIORITY | DELAYED] ????[INTO] tbl_name ????[PARTITION (partition_name,...)] ????[(col_name,...)] ????{VALUES | VALUE} ({expr | DEFAULT},...),(...),... Or: REPLACE [LOW_PRIORITY | DELAYED] ????[INTO] tbl_name ????[PARTITION (partition_name,...)] ????SET col_name={expr | DEFAULT}, ... Or: REPLACE [LOW_PRIORITY | DELAYED] ????[INTO] tbl_name ????[PARTITION (partition_name,...)]? ????[(col_name,...)] ????SELECT ...

原理

replace的工作機制有點像insert,只不過如果在表里如果一行有PRIMARY KEY或者UNIQUE索引，那么就會把老行刪除然后插入新行。如：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

root@test 03:23:55>show create table lingluo\G *************************** 1. row *************************** ???????Table: lingluo Create Table: CREATE TABLE `lingluo` ( ??`a` int(11) NOT NULL DEFAULT '0', ??`b` int(11) DEFAULT NULL, ??`c` int(11) DEFAULT NULL, ??`d` int(11) DEFAULT NULL, ??PRIMARY KEY (`a`),--------------------------同時存在PK約束 ??UNIQUE KEY `uk_bc` (`b`,`c`)----------------唯一索引約束 ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.01 sec) root@test 02:01:44>select * from lingluo; Empty set (0.00 sec) root@test 03:27:40>replace into lingluo values(1,10000,3,4);--------表里沒有已存在的記錄相當于insert Query OK, 1 row affected (0.00 sec)-----------------------affect_rows是1

binlog格式：

1 2	root@test 02:11:18>replace into lingluo values(1,10000,3,5);-------已經存在記錄，且PK和UK同時沖突的時候，相當于先delete再insert Query OK, 2 rows affected (0.00 sec)----------------------affect_rows是2，是delete和insert行數的總和

binlog格式：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

root@test 02:26:09>select * from lingluo; +---+-------+------+------+ | a | b???? | c??? | d??? | +---+-------+------+------+ | 1 | 10000 |??? 3 |??? 5 | +---+-------+------+------+ 1 row in set (0.00 sec) root@test 02:31:54>replace into lingluo values(1,10000,4,5);-------已經存在記錄，且PK同時沖突的時候，相當于先delete再insert Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2，是delete和insert行數的總和 root@test 02:32:02>select * from lingluo; +---+-------+------+------+ | a | b???? | c??? | d??? | +---+-------+------+------+ | 1 | 10000 |??? 4 |??? 5 | +---+-------+------+------+

binlog格式：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

root@test 02:37:04>replace into lingluo values(4,10000,6,5); Query OK, 1 row affected (0.00 sec) root@test 02:37:59>replace into lingluo values(6,10000,6,5);-------已經存在記錄，且UK同時沖突的時候，直接update Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2 root@test 02:40:31>select * from lingluo; +---+-------+------+------+ | a | b???? | c??? | d??? | +---+-------+------+------+ | 1 | 10000 |??? 4 |??? 5 | | 3 | 10000 |??? 5 |??? 5 | | 6 | 10000 |??? 6 |??? 5 | +---+-------+------+------+ 3 rows in set (0.00 sec)

疑問：

既然uk沖突的時候是update，那么為什么affect_rows都是2呢？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

指定列replace: root@test 03:34:37>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 1 |? 126 | |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 |??? 2 |? 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 03:34:37>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 1 |? 126 | |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 |??? 2 |? 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 03:34:40>replace into u (age,d)values(0,130); Query OK, 2 rows affected, 1 warning (0.01 sec) root@test 03:40:39>show warnings; +---------+------+-----------------------------------------+ | Level?? | Code | Message???????????????????????????????? | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'id' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) root@test 03:40:47>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 0 |? 130 |-----------------因為id是parimary但是沒有auto_creasement,由126變成130 |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 |??? 2 |? 129 | +----+------+------+ 6 rows in set (0.00 sec)

用的時候需要注意的是：

如果指定replace列的話，盡量寫全，要不然沒有輸入值的列數據會被賦成默認值（因為是先delete在insert），就和普通的insert是一樣的，所以如果你要執行replace語句的話是需要insert和delete權限的。

如果你需要執行?SET?col_name?=?col_name?+ 1,就相當于執行col_name?= DEFAULT(col_name) + 1.

replace語句如果不深入看的話，就和insert一樣，執行完后沒什么反應

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

root@test 04:20:04>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 0 |? 130 | |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 |??? 2 |? 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 04:20:10>replace into u (id,d) values(8,232); Query OK, 1 row affected (0.01 sec) root@test 04:20:39>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 0 |? 130 | |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 |??? 2 |? 129 | |? 8 | NULL |? 232 | +----+------+------+ 7 rows in set (0.00 sec) root@test 04:20:43>replace into u (id,d) values(7,232); Query OK, 3 rows affected (0.01 sec)----------注意這里affect_rows是3,因為主鍵7已經存在，唯一索引232已經存在，所以需要刪除id為7和8的行，然后插入新行 root@test 04:20:52>select * from u; +----+------+------+ | id | age? | d??? | +----+------+------+ |? 0 |??? 0 |? 130 | |? 1 |??? 0 |??? 1 | |? 3 |??? 1 |? 123 | |? 4 |??? 1 |? 127 | |? 5 |??? 0 |?? 12 | |? 7 | NULL |? 232 | +----+------+------+ 6 rows in set (0.00 sec) root@test 04:20:55>

MySQL給replace和load data....replace用的算法是:

嘗試向表里插入新行

當表里唯一索引或者primary key沖突的時候：

a. delete沖突行

b.往表里再次插入新行

如果遇到重復行沖突，存儲過程很可能當作update執行，而不是delete+insert，但是顯式上都是一樣的。這里沒有用戶可見的影響除了存儲引擎層Handler_xxx的狀態變量。

因為REPLACE ... SELECT語句的結果依賴于select的行的順序，但是順序沒辦法保證都是一樣的，有可能從master和slave的都不一樣。正是基于這個原因，MySQL 5.6.4以后，REPLACE ... SELECT語句被標記為基于statement的復制模式不安全的。基于這個變化，當使用STATEMENT記錄二進制日志的時候，如果有這樣的語句就會在log里面輸出一個告警,同樣當使用MIXED行復制模式也會記錄告警。

在MySQL5.6.6之前的版本，replace影響分區表就像MyISAM使用表級鎖鎖住所有的分區表一樣。當使用?REPLACE ... PARTITION語句時確實會發生上述情況。(使用基于行鎖的InnoDB引起不會發生這種情況。)在MySQL 5.6.6以后的版本MySQL使用分區鎖，只有當分區(只要沒有分區表的列更新)包含了REPLACE語句并且WHERE實際匹配到的才會鎖住那個分區;否則的話就會鎖住整個表。

操作形式：

binlog格式：

結論

當存在pk沖突的時候是先delete再insert

當存在uk沖突的時候是直接update

那了解了這個，對我們有什么用呢？

舉兩個例子：
1. 主備復制

在主備復制的時候，row模式會對replace?into語句產生increment主鍵的自增長，主從兩邊不一致問題。
主庫上如上執行后，備庫里如果是auto_increment是不會變的！這會有什么問題呢？把這個 slave 提升為 master 之后，由于 AUTO_INCREMENT 比實際的 next id 還要小，寫入新記錄時就會發生 duplicate key error，每次沖突之后 AUTO_INCREMENT += 1，直到增長為 max(id) + 1 之后才能恢復正常。
那么對于這種問題的解決辦法是什么呢？@ 小強-zju 同學已經在這里給出了答案: http://bugs.mysql.com/bug.php?id=73563?
2. 數據遷移?
莫名其妙發現有些字段的值被覆蓋
鑒于此，很多使用 REPLACE INTO 的場景，實際上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE，在正確理解 REPLACE INTO 行為和副作用的前提下，謹慎使用 REPLACE INTO。

總結

以上是生活随笔為你收集整理的慎用MySQL replace语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Java实现定时调度的三种方法
下一篇： Redis 作为缓存服务器的配置