清理disuz垃圾用户信息SQL语句

-- hadoop开头的是UCenter表，cdb开头的是BBS部分的表

-- 搜索出所有垃圾用户名:

select `hadoop_members`.`username` from `hadoop_members` left join `cdb_members` on `hadoop_members`.`username`=`cdb_members`.`username` where `cdb_members`.`username` is null;

select `hadoop_members`.`username` from `hadoop_members` where not exists (select `cdb_members`.`username` from `cdb_members` where `hadoop_members`.`username`=`cdb_members`.`username`);

-- 搜索出垃圾用户个数:

select count(`hadoop_members`.`username`) from `hadoop_members` left join `cdb_members` on `hadoop_members`.`username`=`cdb_members`.`username` where `cdb_members`.`username` is null;

select count(`hadoop_members`.`username`) from `hadoop_members` where not exists (select `cdb_members`.`username` from `cdb_members` where `hadoop_members`.`username`=`cdb_members`.`username`);

-- 删除垃圾用户

delete from `hadoop_members` where not exists (select 1 from `cdb_members` where `hadoop_members`.`username`=`cdb_members`.`username`);

    本文转自eyjian 51CTO博客，原文链接：http://blog.51cto.com/mooon/909768，如需转载请自行联系原作者