如何使用mysql去除表中重复的字段

简介：

此处的建表题目来自我们的也门哥Maged，非常感谢他出的这些测试题目，让我能够独立思考，反复试去找到cw2的正确做法。

数据库准备：

害怕被好homi被刺然后被 academic warning 所以浅浅打个码。

1. 创建好这张表后我们我们可以显示创建一个自增主键（如若不创建mysql会隐式创建一个主键），方便我们后续操作。

2. ALTER TABLE roles
       ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

问题解决步骤：

此时我们可以使用 

SELECT RoleID, COUNT(*) as count
FROM roles
GROUP BY RoleID
HAVING count > 1;

来查询重复的行数据。

此时我们会引出一个问题：我们查找到了重复数据，如何删除呢？

这时就可以使用上我们创建的主键id，由于该表roleid不满足唯一性，所以它不可能是主键id，所以我们可以使用一个聚合函数 min()，来确定最小id的重复数据行。

select
    min(id)
from roles
group by RoleID
having count(*) > 1;

查询出数据后我们只需要结合上面两次查询到的数据，使用delete语句，删除查询到的重复数据即可。但是我们不可以直接进行删除如：

delete from roles where RoleID in (
        select RoleID
        from roles
        group by RoleID
        having count(*) > 1
    )
and id not in(
        select min(id) as min_id
        from roles
        group by RoleID
        having count(*) > 1
    )

这样会报错：You can't specify target table 'user_info' for update in FROM clause

这是因为 MySQL 数据库不允许在 DELETE 语句的子查询中直接引用目标表，而通过在子查询外部再包裹一层查询，可以绕过这个限制。可以使用额外的子查询层级，将原始子查询的结果作为临时表，然后在外部的 DELETE 语句中引用这个临时表。

delete from roles where RoleID in (
    select X.* from (
        select RoleID
        from roles
        group by RoleID
        having count(*) > 1
        )as X
    )
and id not in(
    select min_id from (
        select min(id) as min_id
        from roles
        group by RoleID
        having count(*) > 1
        ) as y
    )

如此一来即可删除掉重复的数据。

然后使用

ALTER TABLE roles
    drop column id;

删除掉id字段，并且使用

ALTER TABLE roles
ADD CONSTRAINT pk_roles PRIMARY KEY (RoleID);

即可将RoleID设置为roles表主键id，并且其会自动创建索引。