MySQL 重複資料刪除資料

標籤：

昨天遇到一個問題，就是需要刪掉一個表裡的重複資料，還有就是希望這個表的ID能夠連續，因為一旦刪掉重複記錄，作為自增主鍵的ID就會不連續了，所以就要想辦法搞定

表中資料：

我想要把名字重複的去掉，但是還希望ID能夠連續。

首先是將資料庫裡邊的重複記錄刪掉，我看網上有好多答案是這樣的：

1 delete from people 2 where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 3 and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 

但其實我每次運行這條語句都是行不通的，會報錯：

SQL 錯誤 [1093] [HY000]: You can‘t specify target table ‘test1‘ for update in FROM clause
java.sql.SQLException: You can‘t specify target table ‘test1‘ for update in FROM clause

去網上查過好像是說update以及delete操作沒辦法跟查詢操作一起做的，我看過有的更新的跟查詢的一起做的好像是給查出來的那部分起個別名，然後進行更新就可以了，但是刪除這個我起了別名也不對，不知道是我寫錯還是不行，我就跳過這個方法了。

我用的方法是：先查出資料庫中的重複記錄的資料中的一條，這個不難，很簡單的，sql語句如下：

select * from test1 where name in (select  name from test1  group  by  name   having  count(name) > 1) and id in (select min(id) from  test1  group by name  having count(name)>1) 

結果如下：

id |name |phont |
---|--------|-------|
1 |name22 |123 |
3 |name222 |123 |
5 |name2 |123123 |
8 |123 |123123 |
11 |name1 |123123 |
13 |111 |1231 |
14 |112 |1232 |

這些都是不重複的，換句話說都是要保留的，不被刪掉的，而其餘與這些結果中name相同的應該被刪掉。

也就是說將上邊那個sql語句id後邊加一個not ，查出來的結果就是要刪掉的：結果如下

id |name |phont |
---|--------|-------|
2 |name22 |123 |
4 |name222 |123 |
6 |name2 |123123 |
7 |name2 |NULL |
9 |123 |123123 |
10 |123 |123123 |
12 |name1 |123123 |
15 |111 |1233 |
16 |112 |1234 |
17 |111 |1235 |
18 |112 |1236 |

我把這些需要刪掉的存到另外一個表裡，然後我建立一個test2表，結構複製test1的結構就好了

1 CREATE TABLE `test2` (2   `id` int(11) NOT NULL AUTO_INCREMENT,3   `name` varchar(50) DEFAULT NULL,4   `phont` varchar(50) DEFAULT NULL,5   PRIMARY KEY (`id`)6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

然後插入語句是：

1 insert into test2(2 select * from test.test1 where name in (select  name from test.test1  group  by  name   having  count(name) > 1) 3 and id not in (select min(id) from  test.test1  group by name  having count(name)>1) 4 )

然後test2的表裡的資料就是這樣的：

 

 那接下來做的就是刪掉test1表裡邊與test2表的id相同的資料。

1 delete a.* from test1 a, test2 b where a.id = b.id ;

這樣，test1裡邊的資料就變成了：

這樣的結果就是完全不重複的，但是我還想要他們的id是連續的，而不是這樣的斷開的。

我的做法是將這個表的除掉id之外的所有欄位查出插入到另外一個表test3中，當然，test3要設定id為自增主鍵，但是不插入id，讓它自增，就連續了

當然要建立表test3啦，不過把上邊建立的test2那個複製下來改名字為test3就好啦。

然後插入：

1 insert into test3(name, phont)2 (select name, phont from test2)

 test3表裡的結果就是：

 

這樣就可以把test3改成你想要的名字，然後刪掉test1和test2了，大功告成~

不過感覺還可以就是將已經刪掉重複資料的表test1的資料全都匯出來，一般的資料庫連接工具都有這樣的功能，導成sql格式的，然後建立一個表，比test1多增一個自增主鍵欄位叫NewId欄位，但是Id欄位不能再自增了，然後將導成的sql檔案匯入，不過那個sql檔案可能要編輯一下，改一下自增主鍵id變為普通的欄位什麼的，然後到新表了之後，刪掉id欄位，修改NewId為Id，應該也可以，但是這個方法我沒試過，原先預想過要這麼做但是沒有這麼做，估計以後可以試試，但是感覺兩種的麻煩程度都差不多啊，但是如果將sql語句寫下來之後可能還是第一種方法比較快一點吧。

MySQL 重複資料刪除資料