背景
最近運(yùn)維同學(xué)反饋了一條慢查詢(xún)的問(wèn)題舶斧。研究完表結(jié)構(gòu)后，發(fā)現(xiàn)是缺少索引剑肯。
看完了代碼邏輯捧毛，發(fā)現(xiàn)這張表需要添加唯一索引。本想直接添加一個(gè)唯一索引解決問(wèn)題让网。
但是呀忧，表中的數(shù)據(jù)已經(jīng)存在重復(fù)數(shù)據(jù)。直接添加索引添加不成功溃睹。
常規(guī)思維而账，是去除掉重復(fù)數(shù)據(jù)，然后再添加唯一索引因篇。
問(wèn)題描述
如何去除表中重復(fù)數(shù)據(jù)泞辐？
解決辦法
方法一：添加唯一索引
ALTER IGNORE TABLE `tbl_like_work` ADD UNIQUE INDEX `uk_tian`(`user_id`, `work_id`)
需要注意SQL語(yǔ)句中使用了 IGNORE 關(guān)鍵字笔横，若不添加此關(guān)鍵字，則添加唯一索引會(huì)失敗咐吼。
若添加索引成功吹缔，則自動(dòng)會(huì)去除重復(fù)數(shù)據(jù)。
IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.
方法二：使用臨時(shí)表
對(duì)于 MySQL 5.7.4 是不支持 INSERT IGNORE的锯茄∠崽粒可以使用臨時(shí)表完成數(shù)據(jù)去重。
通過(guò)使用臨時(shí)表肌幽，備份數(shù)據(jù)晚碾。然后給原表添加唯一索引，再將臨時(shí)表中的數(shù)據(jù)導(dǎo)入到原表喂急，得到去重表格嘁。
整個(gè)流程如下：
- 使用臨時(shí)表備份數(shù)據(jù)。根據(jù)原表創(chuàng)建臨時(shí)表
- 清掉原表數(shù)據(jù)
- 給原表添加唯一索引
- 將臨時(shí)表數(shù)據(jù)插入到原表廊移，插入時(shí)忽略重復(fù)數(shù)據(jù)（使用 IGNORE 關(guān)鍵字）
- 刪除臨時(shí)數(shù)據(jù)
CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;
方法三：刪除重復(fù)數(shù)據(jù)
如果需要?jiǎng)h除重復(fù)數(shù)據(jù)糕簿，必須找到重復(fù)數(shù)據(jù)。
如何查找重復(fù)數(shù)據(jù)
SELECT *, count(*) as total
FROM `tbl_like_work`
GROUP BY user_id,work_id
HAVING total > 1
刪除重復(fù)數(shù)據(jù)
DELETE tbl_like_work
WHERE `id` not in (
SELECT min(id) # 保留最舊的數(shù)據(jù)
FROM `tbl_like_work`
GROUP BY user_id,work_id
)
總結(jié)
需要注意的是画机，方法二會(huì)更改原表的主鍵ID冶伞，對(duì)于有主鍵依賴(lài)的業(yè)務(wù)。不能使用第二種方法步氏。
建議使用方法一、方法三徒爹，只刪除重復(fù)數(shù)據(jù)荚醒，且不會(huì)對(duì)原數(shù)據(jù)做出修改。
