MySQL大量臟數(shù)據(jù)如何只保留最新的一條(最新推薦)
因為系統(tǒng)的一個Bug,導致數(shù)據(jù)庫表中出現(xiàn)重復數(shù)據(jù),需要做的是刪除重復數(shù)據(jù)且只保留最新的一條數(shù)據(jù)。
具體場景是這樣的
有張訂單關聯(lián)額外費用表,而且一個訂單號(order_no)記錄只能關聯(lián)同一個費用(cost_id)一次,但是數(shù)據(jù)庫中出現(xiàn)了同一個訂單號關聯(lián)同一個費用n次
當然有人會說上面的問題我們可以建一個 order_no
+ cost_id
的組合唯一索引,這樣就算代碼有bug但至少數(shù)據(jù)庫表中不會有臟數(shù)據(jù)。
似乎這樣就可以了,然而事情并沒有那么簡單。
因為我們表中的數(shù)據(jù)在刪除的時候不會真的的刪除,而是采用邏輯刪除,會有一個 deleted
字段使用0,1標識未刪除與已刪除。
當然 我們也可以考慮將 order_no
+ cost_id
+ deleted
組合成一個聯(lián)合唯一索引。
這樣就ok了嗎?
其實會有一個新的問題,就是如果同一個訂單同一個費用如果被刪除一次。再去刪除會發(fā)現(xiàn)無法成功進行此操作,因為該條數(shù)據(jù)已經(jīng)存在了,不能在刪除了。
所以當時我們并沒有建立聯(lián)合唯一索引,才導致臟數(shù)據(jù)的產(chǎn)生。
其實上面這種場景網(wǎng)上有個比較好的解決方案,就是我們依舊可以將 order_no + cost_id + deleted 組合成一個聯(lián)合唯一索引,
但是刪除的時候deleted不再是固定的1,而是當前的主鍵ID,也就是deleted不等于0都是刪除狀態(tài),如果刪除了那deleted值=id
言歸正傳,接下來我們來講下該如何修復臟數(shù)據(jù)的問題
我們先創(chuàng)建一張訂單關聯(lián)費用表
CREATE TABLE `order_cost_detail` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵', `order_no` varchar(32) NOT NULL COMMENT '訂單號', `cost_id` int NOT NULL COMMENT '費用Id', `cost_name` varchar(50) NOT NULL DEFAULT '' COMMENT '費用名稱', `money` decimal(10,2) NOT NULL COMMENT '金額', `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間', `deleted` tinyint(1) NOT NULL COMMENT '是否刪除(0 否,1 是)', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='訂單 - 費用表';
插入一些模擬數(shù)據(jù)
INSERT INTO `order_cost_detail` (`id`, `order_no`, `cost_id`, `cost_name`, `money`, `create_time`, `deleted`) VALUES (1, 'EX202208160000012-3', 2, '停車費', 100.00, '2022-08-19 11:30:48', 0), (2, 'EX202208160000012-4', 3, '停車費', 100.00, '2023-02-17 11:25:27', 0), (3, 'EX202208160000012-4', 3, '停車費', 200.00, '2023-02-17 11:25:28', 0), (4, 'EX202208170000002-1', 1, '路橋費', 300.00, '2022-08-19 11:31:57', 0), (5, 'EX202208170000002-1', 1, '路橋費', 450.00, '2022-08-19 11:32:57', 0), (6, 'EX202208180000002-1', 2, '高速費', 225.00, '2022-08-19 11:35:41', 0);
我們的目的很明確,就是要刪除 多余的同一訂單號費用相同的數(shù)據(jù),同時保留最新的一條數(shù)據(jù)。
我們可以先用sql看下是否有重復數(shù)據(jù)
SELECT order_no, cost_name, count(*) AS num FROM order_cost_detail WHERE deleted = 0 GROUP BY order_no, cost_name HAVING num > 1
運行結果
發(fā)現(xiàn)有兩個訂單有臟數(shù)據(jù),如果實際生產(chǎn)只有兩條臟數(shù)據(jù)那簡單,直接查詢這兩個訂單,把重復數(shù)據(jù)刪掉就好了。
但如果有幾十條甚至上百條數(shù)據(jù)呢,總不能一條一條的刪吧。
一般我們刪除重復數(shù)據(jù)都會保留最新的那條,所以我們可以這樣做
如果主鍵是自增的,那么重復數(shù)據(jù)刪除的時候,主鍵最大的一條就是需要保留的,如果主鍵不是自增的,我們可以根據(jù)創(chuàng)建時間,保留創(chuàng)建時間最大的記錄
我們先看下,我們需要刪除的記錄
select * from order_cost_detail where id not in ( select max(id) as num from order_cost_detail where deleted = 0 group by order_no, cost_name )
查詢結果
根據(jù)結果來看確實是這兩條記錄需要刪除,那么我們開始執(zhí)行刪除操作
sql如下
-- 這里是邏輯刪除,也就是將需要刪除的數(shù)據(jù)打上deleted = 1 標記 update order_cost_detail set deleted = 1 where id in ( select id from order_cost_detail where id not in ( select max(id) as num from order_cost_detail where deleted = 0 group by order_no, cost_name ) )
執(zhí)行的時候發(fā)現(xiàn)報錯了
You can't specify target table 'order_cost_detail' for update in FROM clause
它的意思是說,不能在同一語句中,先select出同一表中的某些值,再update這個表,即不能依據(jù)某字段值做判斷再來更新某字段的值。
這個問題在MySQL官網(wǎng)中有提到解決方案:拉到文檔下面 https://dev.mysql.com/doc/refman/8.0/en/update.html
解決方法:select 的結果再通過一個中間表 select 多一次,就可以避免這個錯誤
update order_cost_detail set deleted = 1 where id in ( select t.id from ( select id from order_cost_detail where id not in ( select max(id) as num from order_cost_detail where deleted = 0 group by order_no, cost_name ) ) t )
執(zhí)行成功
阿里巴巴手冊索引規(guī)范,第一條就是
【強制】業(yè)務上具有唯一特性的字段,即使是組合字段,也必須建成唯一索引。
說明
:不要以為唯一索引影響了insert速度,這個速度損耗可以忽略,但提高查找速度是明顯的:另外,即使在應用層做了非常完善
的校驗和控制,只要沒有唯一索引,根據(jù)墨菲定律,必然有臟數(shù)據(jù)產(chǎn)生。
到此這篇關于MySQL大量臟數(shù)據(jù),如何只保留最新的一條?的文章就介紹到這了,更多相關MySQL保留最新的一條內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL如何根據(jù)不同條件聯(lián)查不同表的數(shù)據(jù)if/case
這篇文章主要介紹了MySQL如何根據(jù)不同條件聯(lián)查不同表的數(shù)據(jù)if/case問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-05-05MySQL里的found_row()與row_count()的解釋及用法
MySQL中有兩個函數(shù)來計算上一條語句影響了多少行,不同于SqlServer/Oracle,不要因為此方面的差異而引起功能問題2013-02-02