清理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，如需轉載請自行聯系原作者