初始化實驗環境明確需求查找重複的數據查找要保留的數據刪除重複的數據方法一方法二方法三寫法1寫法2總結

MySQL中經常會遇到重複的數據，那麼當我們遇到重複的時候的時候，如果定位哪些數據是有重複的記錄？如何刪除重複的數據？我們該怎麼做呢？接下來我們一步步來分析一下遇到這樣的情況後，該如何處理。

咋辦呢？

初始化實驗環境

我們創建一個簡單的表user_info，然後基於這個表進行分析重複數據的處理情況。其中的id為自增主鍵，name、sex、age三個列是我們判斷是否為重複數據的key，如果這三列的值相同，則認為這行數據為重複數據。建表語句如下：

CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

初始化數據如下：

INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (1, 'A', '男', 22, '第一個A');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (2, 'B', '女', 33, '第一個B');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (3, 'C', '男', 44, '第一個C');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (4, 'D', '女', 55, '第一個D');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (5, 'A', '男', 22, '第二個A');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (6, 'B', '女', 33, '第二個B');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (7, 'C', '男', 44, '第二個C');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (8, 'D', '女', 55, '第二個D');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (9, 'E', '男', 18, '第一個E');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (10, 'A', '男', 22, '第三個A');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (11, 'B', '女', 33, '第三個B');
INSERT INTO `tmp_test`.`user_info`(`id`, `name`, `sex`, `age`, `remark`) VALUES (12, 'F', '男', 15, '第一個F');

最後表中數據如下:

初始化的測試數據

明確需求

假設我們的要求是保留重複數據中，第一次出現的數據，後面出現的數據不保留。

也就是我們的上面的這個表中每一組重複數據中id最小的一行數據需要保留，其他比較大的id的重複的數據行需要被刪除。當然如果是要保留id行最大的一行數據最為最後的數據行也是可以了，只要在查詢的時候，稍微修改一下SQL語句的min(id)或max(id)函數即可。

開整

查找重複的數據

基於前面我們初始化的實驗數據，首先我們要查詢出那些數據是有重複數據的行，通過下面的SQL語句，可以得到結果：其中有重複數據的是name值為A、B、C、D的四種類型的數據。使用如下SQL可以查詢出來那些數據行有重複記錄，並統計出重新出現的次數。

select 
	name, sex, age, 
  count(*) as count -- 數據重複出現的次數
from user_info 
group by name, sex, age 
having count(*) > 1;

重複數據在表中的統計結果如下：

數據重複的統計情況

查找要保留的數據

上面我們知道該如何查詢哪些數據是重複數據了，那麼我們需要保留的數據是哪些？使用下面的SQL既可以獲取到我們要保留的數據行：

select * from user_info 
where id in (
	select 
		min(id) 
	from user_info 
	group by name, sex, age
);

結果如下：

每組重複數據中，id值最小的數據行

上面的結果就是我們需要最後留下來的數據。這裡包含了非重複的時候和每一組重複的數據中id最小的數據行。

刪除重複的數據

方法一

這是最笨的一種方式，也是最容易理解的一種方式，效率也比較低。思路如下：

步驟一

步驟二

步驟三

從上面的過程中，我們一步一步定位到了我們需要刪除的數據是哪些。定位到這些數據之後，刪除的時候，只要把查詢語句改為刪除語句即可。所以最後通過這樣的方式來刪除我時候，我們的刪除語句如下：

delete from user_info 
where (name,sex,age) in ( 
	select x.* from ( -- 刪除的時候，這裡要在包裹一層子查詢
		select -- 查詢重複數據中，name, sex, age的值
			name, sex, age
		from user_info
		group by name, sex, age
		having count(*) > 1
	) as x
)
and id not in (
	select min_id from ( -- 刪除的時候，這裡要在包裹一層子查詢
		select -- 查詢重複數據中，最小的id值
			min(id) as min_id
		from user_info 
		group by name, sex, age
		having count(*) > 1
	) as y
);

注意：上面的刪除語句中，我們在兩個where條件中的子查詢語句外面又包裹了一層子查詢，即為上面SQL語句中的as x和as y兩個查詢語句，之所以包裹一層的原因是在程序如下的錯誤提示：

1093 - You can't specify target table 'user_info' 
for update in FROM clause, Time: 0.084000s

上述錯誤的原因是：修改一個表的時候子查詢不能是這被修改的這個表，所以，我們的解決辦法是，在子查詢外面再套一層查詢語句就可以了。

方法二

上面方法一的思路是想辦法找到我們要刪除的數據是哪些，然後我們在刪除的時候，使用where條件去匹配這些查詢出來要刪除的數據行，以此來達到刪除重複數據的目的。

換個思路解決

此時，我們不妨換一個角度思考：我們不要去關注哪些是我們需要刪除的重複數據，相反，我們去關注哪些是我們需要留下來的數據。然後我們可以在刪除的時候，使用取反的方式not in我們需要保留下來的數據，那不是就我們需要刪除的數據嗎？

所以，我們想一想哪些使我們需要留下來的數據呢？每一組數據中，id值最小的哪一行就是我們要保留的數據行。其餘的我們就不關心了。那麼怎麼樣才能取到這樣的數據行呢？使用下面的SQL語句可以獲取我們需要保留的數據行的所有的id的值：

select 
	min(id) 
from user_info 
group by name, sex, age;

結果如下：

既然我們想要保留的數據行的id集合得到了，在我們要刪除數據的where條件中，使用not in我們要保留的id集合，不就是需要刪除的數據嗎？刪除重複數據的語句如下：

delete from user_info 
where id not in(
	select min_id from (
		select 
			min(id) as min_id
		from user_info 
		group by name, sex, age
	) as x
);

注意：這裡為了避免MySQL的1903錯誤，我們也在where條件的子查詢中包裹了另外一個子查詢，即上面SQL中as x查詢語句。

方法三

尋找更高效簡單的方法

通過兩個表關聯的方式來刪除數據，這個方式效率比較高，推薦使用這種方式。自己和自己關聯，關聯的條件就是我們判斷數據是否為重複數據的key。除此之外，最重要的一個條件是：兩個表的id關聯條件，這個是刪除保留數據的關鍵條件。查詢重複數據的SQL語句如下：

select a.*,b.* 
from user_info as a 
inner join user_info as b 
on a.name = b.name 
and a.sex = b.sex 
and a.age = b.age
and  a.id > b.id;

結果如下：

寫法1

刪除重複數據SQL語句如下：

delete a.*
from user_info as a 
inner join user_info as b 
on a.name = b.name 
and a.sex = b.sex 
and a.age = b.age
and  a.id > b.id;

寫法2

除了上面的那種寫法之外，還有另外一種寫法，如下：

查詢待刪除的重複數據SQL如下：

select * from user_info as a 
where a.id <> (
	select 
		min(b.id) 
	from user_info as b 
	where a.name = b.name
	and a.sex = b.sex
	and a.age = b.age
);

刪除重複數據的SQL語句如下：

delete a.* from user_info as a
where a.id <> (
	select
		min(b.id)
	from (
		select * from user_info
	) as b
	where a.`name`= b.`name`
	and a.sex = b.sex
	and a.age = b.age
);

總結

以上是對於MySQL中重複數據刪除的時候，經常使用的方法。對於其他數據庫中存在的重複數據，刪除的思路也是這些，只是具體到SQL語句的寫法可能稍有稍有差異。只要你掌握了思路，具體到SQL語句的寫法，嘗試幾次就可以成功。

以上，希望能幫助到你。

最後提醒一點： 在真正刪除之前，記得對原數據備份一下。以便刪除錯誤後，數據不能恢復回來。可以使用如下的語句來創建一個備份表，以便於在刪除錯誤後，把數據恢復到原來的表中去。

create table user_info_bak as select * from user_info; --創建一個備份表
truncate table user_info; -- 清空原始表中的數據
insert into user_info select * from user_info_bak; -- 從備份表中把數據插入到原始表中

像上面這樣操作，數據如果刪除失誤的時候，可以從user_info_bak中還原數據到user_info表中。