mysql - 删除 mySQL 5.7.9 中的所有重复行

Question

我有这样的 table

 select id,channel,post from posts limit 10
+------+------------+-------+
| id   | channel    | post  |
+------+------------+-------+
| 1433 | channel2   | 19353 |
| 1434 | channel2   | 19353 |
| 1435 | channel2   | 19354 |
| 1436 | channel2   | 19354 |
| 1437 | channel2   | 19356 |
| 1438 | channel2   | 19357 |
| 1439 | channel2   | 19358 |
| 1440 | channel2   | 19359 |
| 1441 | channel2   | 19360 |
| 1634 | channel2   | 19360 |
+------+------------+-------+

在那个表 id是 primary key ，现在在那个表中我有一个 channel 的重复帖子，我尝试添加一个 unique key使用此查询表并删除所有重复行

ALTER ignore TABLE `posts` ADD UNIQUE key `unique_index` (`channel`, `post`);

但在 mysql 5.7.9我们不能那样做!

所以我想知道如何删除重复行并为 channel 添加唯一键, post
解决方案

    DELETE FROM posts
WHERE ID Not in (SELECT* 
                 FROM (SELECT MIN(ID)
                       FROM posts 
                       GROUP BY channel, Post) B
                ) 

Answer 1

您不能在删除的子查询中使用同一个表。
所以你需要使用额外的交付表来解决这个问题。

DELETE FROM
 posts 
WHERE
 posts.id NOT IN (  
  SELECT 
   id
  FROM (    
    SELECT 
      MIN(id) AS id
    FROM
      posts 
    GROUP BY
      posts.channel
    , posts.post
  ) 
   AS 
    posts_id    
)