MySQL大量臟數據怎么只保留最新的一條

本文小編為大家詳細介紹“MySQL大量臟數據怎么只保留最新的一條”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL大量臟數據怎么只保留最新的一條”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

因為系統的一個Bug，導致數據庫表中出現重復數據，需要做的是刪除重復數據且只保留最新的一條數據。

具體場景是這樣的

有張訂單關聯額外費用表,而且一個訂單號(order_no)記錄只能關聯同一個費用(cost_id)一次，但是數據庫中出現了同一個訂單號關聯同一個費用n次

當然有人會說上面的問題我們可以建一個 order_no + cost_id 的組合唯一索引，這樣就算代碼有bug但至少數據庫表中不會有臟數據。

似乎這樣就可以了，然而事情并沒有那么簡單。

因為我們表中的數據在刪除的時候不會真的的刪除，而是采用邏輯刪除，會有一個 deleted 字段使用0，1標識未刪除與已刪除。

當然 我們也可以考慮將 order_no + cost_id + deleted 組合成一個聯合唯一索引。

這樣就ok了嗎？

其實會有一個新的問題，就是如果同一個訂單同一個費用如果被刪除一次。再去刪除會發現無法成功進行此操作，因為該條數據已經存在了，不能在刪除了。

所以當時我們并沒有建立聯合唯一索引，才導致臟數據的產生。

其實上面這種場景網上有個比較好的解決方案，就是我們依舊可以將 order_no + cost_id + deleted 組合成一個聯合唯一索引，
但是刪除的時候deleted不再是固定的1，而是當前的主鍵ID,也就是deleted不等于0都是刪除狀態，如果刪除了那deleted值=id

言歸正傳,接下來我們來講下該如何修復臟數據的問題

我們先創建一張訂單關聯費用表

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 '創建時間',
  `deleted` tinyint(1) NOT NULL COMMENT '是否刪除（0 否，1 是）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1  COMMENT='訂單 - 費用表';

插入一些模擬數據

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);

我們的目的很明確，就是要刪除 多余的同一訂單號費用相同的數據，同時保留最新的一條數據。

我們可以先用sql看下是否有重復數據

SELECT order_no, cost_name, count(*) AS num
FROM order_cost_detail
WHERE deleted = 0
GROUP BY order_no, cost_name
HAVING num > 1

運行結果

發現有兩個訂單有臟數據，如果實際生產只有兩條臟數據那簡單，直接查詢這兩個訂單，把重復數據刪掉就好了。

但如果有幾十條甚至上百條數據呢，總不能一條一條的刪吧。

一般我們刪除重復數據都會保留最新的那條，所以我們可以這樣做

如果主鍵是自增的，那么重復數據刪除的時候，主鍵最大的一條就是需要保留的,如果主鍵不是自增的，我們可以根據創建時間，保留創建時間最大的記錄

我們先看下，我們需要刪除的記錄

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
)

查詢結果

根據結果來看確實是這兩條記錄需要刪除，那么我們開始執行刪除操作

sql如下

-- 這里是邏輯刪除，也就是將需要刪除的數據打上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
		)
	)

執行的時候發現報錯了

You can't specify target table 'order_cost_detail' for update in FROM clause

它的意思是說，不能在同一語句中，先select出同一表中的某些值，再update這個表，即不能依據某字段值做判斷再來更新某字段的值。

解決方法：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
)

執行成功

阿里巴巴手冊索引規范，第一條就是

【強制】業務上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。

說明：不要以為唯一索引影響了insert速度，這個速度損耗可以忽略，但提高查找速度是明顯的：另外，即使在應用層做了非常完善
的校驗和控制，只要沒有唯一索引，根據墨菲定律，必然有臟數據產生。

讀到這里，這篇“MySQL大量臟數據怎么只保留最新的一條”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。