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

最新推荐文章于 2022-02-25 16:38:11 发布

原创最新推荐文章于 2022-02-25 16:38:11 发布 · 7.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #hadoop #join #bbs #delete

点点滴滴专栏收录该内容

25 篇文章

订阅专栏

本文提供了一系列SQL查询示例，用于从两个不同的数据库表中找出并删除未在BBS表中出现的UCenter表中的垃圾用户账号。通过左连接和not exists子句实现了对这些孤立账户的有效识别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 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`);