1. 首頁 > 生活 > >

mysql找出重復的數據 mysql重復數據查詢

小知識



文章插圖
mysql找出重復的數據 mysql重復數據查詢

文章插圖
初始化實驗環境明確需求查找重復的數據查找要保留的數據刪除重復的數據方法一方法二方法三寫法1寫法2總結
MySQL中經常會遇到重復的數據，那么當我們遇到重復的時候的時候，如果定位哪些數據是有重復的記錄？如何刪除重復的數據？我們該怎么做呢？接下來我們一步步來分析一下遇到這樣的情況后，該如何處理 。
咋辦呢？
初始化實驗環境
【mysql找出重復的數據 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, agefrom user_infogroup by name, sex, agehaving count(*) > 1) as x)and id not in (select min_id from ( -- 刪除的時候，這里要在包裹一層子查詢select -- 查詢重復數據中，最小的id值min(id) as min_idfrom user_info group by name, sex, agehaving 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_idfrom 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.ageanda.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.ageanda.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.nameand a.sex = b.sexand a.age = b.age);刪除重復數據的SQL語句如下：
delete a.* from user_info as awhere a.id <> (selectmin(b.id)from (select * from user_info) as bwhere a.`name`= b.`name`and a.sex = b.sexand a.age = b.age);總結
以上是對于MySQL中重復數據刪除的時候，經常使用的方法 。對于其他數據庫中存在的重復數據，刪除的思路也是這些，只是具體到SQL語句的寫法可能稍有稍有差異 。只要你掌握了思路，具體到SQL語句的寫法，嘗試幾次就可以成功 。
以上，希望能幫助到你 。
最后提醒一點： 在真正刪除之前，記得對原數據備份一下 。以便刪除錯誤后，數據不能恢復回來 ?？梢允褂萌缦碌恼Z句來創建一個備份表，以便于在刪除錯誤后，把數據恢復到原來的表中去 。
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表中 。