后台 清空冗余数据

删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

delete from sns_message where not exists ( select * from sns_user where sns_user.uid=sns_message.to_uid);

找出数据库中有某字段的所有表；
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='ipiao20' and COLUMN_NAME ='uid'


查询表中重复记录
select * from sns_friend a
where (a.`uid`,a.`friend_uid`) in (select `uid`,`friend_uid`from sns_friend group by `uid`,`friend_uid`having count(*) > 1)
and `friend_id`not in (select min(`friend_id`) from sns_friend group by `uid`,`friend_uid`having count(*)>1)


delete from sns_friend as a
where ((a.`uid`,a.`friend_uid`) in (select `uid`,`friend_uid`from sns_friend group by `uid`,`friend_uid`having count(*) > 1))
and (`friend_id`not in (select min(`friend_id`) from sns_friend group by `uid`,`friend_uid`having count(*)>1))


delete from sns_friend a where ((a.`uid`,a.`friend_uid`) in

and `friend_id` not in (select min(`friend_id`) from sns_friend group by `uid`,`friend_uid` having count(*)>1)

转载于:https://www.cnblogs.com/ybbqg/archive/2012/05/03/2480847.html