Mysql刪除重複資料並且只保留一條(附範例!)

2023-02-03 18:03:50

（1）以這張表為例：

CREATE TABLE `test`  (
  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '註解id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO test (id,`name`) VALUES (replace(uuid(),'-',''),'張三'),(replace(uuid(),'-',''),'張三');

表裡有兩條資料，然後名字是相同的，但是id是不同的，現在要求是隻留一條資料：

（2）查詢name值重複的資料：

現實開發當中可能一個欄位無法鎖定重複值，可以採取group by多個值！利用多個值來鎖定重複的行資料！

SELECT name FROM test GROUP BY `name` HAVING count( name ) > 1

（3）查詢重複資料裡面每個最小的id：

SELECT min(id) as id FROM test GROUP BY `name` HAVING count( name ) > 1

（4）查詢去掉重複資料最小id的其他資料：也就是要刪除的資料！

SELECT * FROM test 
WHERE name IN ( SELECT name FROM test GROUP BY `name` HAVING count( name ) > 1 ) 
AND 
id NOT IN (SELECT min( id ) FROM test GROUP BY `name` HAVING count( NAME ) > 1)

（5）刪除去掉重複資料最小id的其他資料：

可能這時候有人該說了，有了查詢，直接改成delete不就可以了，真的是這樣嗎？其實不是的，如下執行報錯：

首先明確一點這個錯誤只會發生在delete語句或者update語句，拿update來舉例 : update A表 set A列 = (select B列 from A表)； 這種寫法就會報這個錯誤，原因：你又要修改A表，然後又要從A表查資料，而且還是同層級。Mysql就會認為是語法錯誤！

巢狀一層就可以解決，update A表 set A列 = (select a.B列 from (select * from A表) a); 當然這個只是個範例，這個範例也存在一定的問題，比如(select a.B列 from (select * from A表) a)他會查出來多條，然後賦值的時候會報 1242 - Subquery returns more than 1 row。

巢狀一層他就可以和update撇清關係，會優先查括號裡面的內容，查詢結果出來過後會給存起來，類似臨時表，可能有的人該好奇了，update A表 set A列 = (select B列 from A表)； 我明明加括號了呀，難道不算巢狀嗎，當然不算，那個括號根本沒有解決他們之間的層次關係！

詳解看這篇文章：https://www.jb51.net/article/274025.htm

（6）正確的寫法：

方式一：

DELETE FROM test 
WHERE name IN ( select a.name from (SELECT name FROM test GROUP BY `name` HAVING count( name ) > 1) a) 
AND 
id NOT IN (select a.id from (SELECT  min(id) as id FROM test GROUP BY `name` HAVING count( name ) > 1) a)

注意：刪除之前一定要先查詢，然後再刪除，否則一旦語法有問題導致刪了不想刪除的資料，想要恢復很麻煩！或者刪除前備份好資料，不要嫌麻煩，一旦出問題，才是真正的大麻煩！

方式二：

DELETE FROM test 
WHERE
	id NOT IN (
	SELECT
		t.id 
FROM
	( SELECT MIN(id) as id FROM test GROUP BY NAME ) t)

（7）錯誤的寫法： 這塊我吃過一次虧，所以專門寫出來，避免踩坑！

千萬千萬不能這麼搞，下面這個語法相當於是先按name分組，然後查出來大於1的，這時候假如大於1的有很多，然後外面巢狀的那一層，只取了最小的一條資料，然後再加上使用的是NOT IN，最終會導致資料全部被刪除！！！

執行前有四條資料，實際上我們要的是張三留下來一條，然後李四留下來一條

執行結果：只留下了一條！

總結

到此這篇關於Mysql刪除重複資料並且只保留一條的文章就介紹到這了,更多相關Mysql刪除重複資料只保留一條內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

Mysql刪除重複資料並且只保留一條(附範例!)

熱門文章