Created
August 30, 2019 08:12
-
-
Save chf007/d51b9eb6114ef1d4d98fddcc28af4e5c to your computer and use it in GitHub Desktop.
删除mysql重复数据，并添加唯一索引
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 查询重复数据条数 | |
| SELECT * FROM (SELECT room_id, user_id, COUNT(1) AS num FROM live_room_subscribe GROUP BY room_id, user_id) temp WHERE num > 1; | |
| # 删除除了最小id之外的重复数据 | |
| DELETE | |
| FROM | |
| live_room_subscribe | |
| WHERE | |
| (room_id, user_id) IN ( | |
| SELECT | |
| t.room_id, | |
| t.user_id | |
| FROM | |
| ( | |
| SELECT | |
| room_id, | |
| user_id | |
| FROM | |
| live_room_subscribe | |
| GROUP BY | |
| room_id, | |
| user_id | |
| HAVING | |
| count(1) > 1 | |
| ) t | |
| ) | |
| AND id NOT IN ( | |
| SELECT | |
| tmp_table.tmp_id | |
| FROM | |
| ( | |
| SELECT | |
| min(id) AS tmp_id | |
| FROM | |
| live_room_subscribe | |
| GROUP BY | |
| room_id, | |
| user_id | |
| HAVING | |
| count(1) > 1 | |
| ) tmp_table | |
| ); | |
| # 添加唯一索引 | |
| ALTER TABLE live_room_subscribe ADD UNIQUE INDEX subscribe_unique_index (room_id, user_id); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment