笔记: 统一更新wordpress的评论为垃圾评论

select comment_author, count(1) as cnt from dbx_comments where comment_approved=’0′ group by comment_author order by cnt;

select * from (select comment_author, count(1) as cnt from dbx_comments where comment_approved=’0′ group by comment_author) t where t.cnt>=5;

同一个人未处理的评价超过5次的,把他的未处理评论更新为 trash:
update dbx_comments set comment_approved=’trash’ where comment_approved=’0′ and comment_author in(
select comment_author from (select comment_author, count(1) as cnt from dbx_comments where comment_approved=’0′ group by comment_author) t where t.cnt>=5);

把未处理的评价中包含 http 的评论都更新为 trash:
select * from dbx_comments where comment_approved=’0′ and (comment_content like ‘%http%’ or comment_content like ‘%url%’);
update dbx_comments set comment_approved=’trash’ where comment_approved=’0′ and (comment_content like ‘%http%’ or comment_content like ‘%url%’);

同一IP多次评论的,标记为 ‘trash’:
update dbx_comments set comment_approved=’trash’ where comment_approved=’0′ and comment_author_IP in(
select comment_author_IP from (select comment_author_IP, count(1) as cnt from dbx_comments where comment_approved=’0′ group by comment_author_IP) t where t.cnt>=2);

评论长度超过 200个字符的,标记为 ‘trash’:
update dbx_comments set comment_approved=’trash’ where comment_approved=’0′ and length(comment_content)>200;

评论者邮箱长度超过 30个字符的,标记为 ‘trash’:
update dbx_comments set comment_approved=’trash’ where comment_approved=’0′ and length(comment_author_email)>30;

select * from dbx_comments where comment_approved=’0′ and length(comment_content)>200;

select count(1) from dbx_comments where comment_approved=0;

枚举值:
comment_approved:
0-未处理
1-Approved
spam
trash

Leave a Reply

Your email address will not be published.