MySQL中replace into與replace區別詳解

2022-08-19 14:03:31

0.故事的背景

【表格結構】

CREATE TABLE `xtp_algo_white_list` (
  `strategy_type` int DEFAULT NULL,
  `user_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `status` int DEFAULT NULL,
  `destroy_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `xtp_algo_white_list_UN` (`strategy_type`,`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


# `strategy_type`,`user_name` 這兩個是聯合唯一索引，多關注後續需要用到！！！

【需求：】

根據表格裡面， 209133002266賬戶的資料，重新插入一個使用者20220302001，使得新生成的資料中strategy_type & status & destroy_at 欄位與209133002266使用者的一致。
使用update 一條一條更新也行，但是比較慢。
使用replace into 效果會高很多，但是深入研究發現也有一些坑的地方

1.replace into 的使用方法

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

# replace into 後面跟表格+需要插入的所有欄位名（自動遞增欄位不用寫）
# select 後面選擇的欄位，如果根據查詢結果取值，則寫欄位名；如果是寫死的，則直接寫具體值即可
# 可以理解為，第一部分是插入表格的結構，第二部分是你查詢的資料結果

2.有唯一索引時—replace into & 與replace 效果

step1：第一次執行sql情況

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

【執行完之後，查詢結果如下：】

step2：第二次執行sql情況

為什麼第二次執行的時候，顯示update 12行的資料且created at 資料更新了，而第一次會顯示update 6行？？？

1.因為在執行sql的時候，replace into 其實分了兩個步驟執行。第一步是將查詢到資料轉化為新的資料。第二步，新的資料如果表中已經有相同的內容，則刪除掉。如果沒有相同的內容，則直接插入新的資料。

2.因如上第一次執行的時候，已經生成一次新資料了，第二次會先刪除，再把最新的資料插入進去，最終才顯示update 12 行

step3：第三次執行sql情況

# 此時執行的是replace 

replace xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

最終檢視到的情況與第二次執行的sql一樣。
當新資料已經存在的時候，replace into 與replace是一樣的
後續刪除所有20220302001，執行1次，2次sql，發現replace into 與 replace 效果都是一樣的

【總結：】當有唯一索引限制的時候，如果新增的資料會受限於唯一索引，則資料只會插入一次，如果已經存在則會先刪除再插入。此時replace into 與replace 效果一樣。

3.沒有唯一索引時—replace into 與 replace

我們將strategy_type & user_name 聯合唯一索引刪除，且刪除20220302001使用者所有資料。最終表格結構如下：

CREATE TABLE `xtp_algo_white_list` (
  `strategy_type` int DEFAULT NULL,
  `user_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `status` int DEFAULT NULL,
  `destroy_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1）.replace函數的具體情況

step1：執行如下replace 對應sql：

replace xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

step2：再次執行replace 對應sql：

第二次執行replace 對應sql ，因為沒有唯一索引限制，結果原始資料居然沒變動。又重新生成了新的6條資料。
如果後續還執行如上的sql，則資料還會繼續增加

2）.replace into 函數的具體情況

執行之前，先清理資料，將所有20220302001的資料都刪除掉

step1：執行如下replace into 對應sql：

replace into xtp_algo_white_list (`strategy_type`, `user_name`, `status`, `destroy_at`)
select strategy_type ,20220302001, status, destroy_at from xtp_algo_white_list xawl where xawl.user_name = 209133002266;

step2：再次執行replace into 對應sql：

最終發現，沒有唯一索引的時候，replace into 與replace 居然一摸一樣的效果，都是繼續增加資料。

通過以上分析，沒看出replace into 與replace 具體有啥區別？？？？有誰知道呢？

4.replace的用法

單獨replace的作用是替換欄位中某數值的顯示效果。可以數值中的部分替換、也可以全部替換。
如下表格，將user_name的欄位，20220302改為"A_20220303"顯示，並且新欄位叫做new_name顯示

select *, replace(user_name,20220302,'A_20220303') as "new_name" from xtp_algo_white_list where user_name = 20220302001;

到此這篇關於MySQL中replace into與replace區別詳解的文章就介紹到這了,更多相關MySQL replace into與replace內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！