mysql怎么执行去重复

Question

比如 我有一个库 名字是mydata 要去除库下所有表 username和password 重复字段 只留一个怎么做 

求告知一下怎么执行。

Answer 1

没有唯一主键吗

有的话下面这个最方便了

delete from mydata where id  in  (select min(id) from mydata group by username, password)

Answer 2

delete from mydata where username in
(
	select u.username from
	(
		select username from mydata group by username having count(username) > 1
	)u
)
and id not in
(
	select i.id from
	(
		select min(id) as id from mydata group by username having count(username) > 1
	)i
);
试试看

Answer 3

select distinct username from table

Answer 4

数据库查重复

select username,password,count(*) as count from mydata group by username,password having count>1; 

查出来删除掉就行