|
-- Anti SPAM
|
|
-- 查找所有疑似 SPAM 帐户
|
|
select
|
|
id,
|
|
username,
|
|
domain,
|
|
display_name,
|
|
avatar_file_name
|
|
from
|
|
accounts
|
|
where
|
|
username ~ '^[0-9a-z]{10}$'
|
|
and (username = display_name or display_name = '')
|
|
and created_at > '2024-02-15 00:00:00'
|
|
and suspended_at is null
|
|
and avatar_file_name is null
|
|
and "domain" is not null;
|
|
|
|
-- 查找所有位于未进行管理操作实例的疑似 SPAM 帐户
|
|
select
|
|
id,
|
|
username,
|
|
domain,
|
|
display_name,
|
|
avatar_file_name
|
|
from
|
|
accounts
|
|
where
|
|
username ~ '^[0-9a-z]{10}$'
|
|
and (username = display_name or display_name = '')
|
|
and created_at > '2024-02-15 00:00:00'
|
|
and suspended_at is null
|
|
and avatar_file_name is null
|
|
and "domain" is not null
|
|
and "domain" not in (
|
|
select
|
|
db."domain"
|
|
from
|
|
domain_blocks db
|
|
);
|