批量删除垃圾邮件 Wordpress 用户的 MySQL 查询 [英] MySQL Query to Bulk Delete Spam Wordpress Users

问题描述

我的会员网站充斥着垃圾邮件注册.我注意到的一件事是，很多垃圾邮件发送者的电子邮件都是 @hotmail.com.

My membership website has been inundated with spam sign ups. One thing I noticed is a lot of the spammers emails are @hotmail.com.

我想要做的是删除所有订阅者并拥有@hotmail 电子邮件地址的用户.

What I want to do is delete all users who are subscribers and have a @hotmail email address.

用户数据在两个表 wp_users 和 wp_usermeta 中，据我所知，我需要删除两个表中的数据才能有效地删除用户.我还没有找到一个查询，可以从两个表中的 mysql 中删除所有用户数据.

The users data are in two tables wp_users and wp_usermeta and as far as I understand, I need to delete the data in both tables to effectively remove the users. I haven't managed to find a query that can delete all users data from mysql across the two tables.

我可以用这个查询删除 wp_user 表中的用户

I can delete the users in the wp_user table with this query

DELETE 
FROM  wp_users 
WHERE  user_email LIKE  "%@hotmail%"

但我还需要从 wp_usersmeta 表中删除数据，并确保我只删除订阅者(meta_key = wp_capabilities and meta_value =subscriber).

But I also need to delete the data from the wp_usersmeta table and also make sure I am only deleting subscribers (meta_key = wp_capabilities and meta_value = subscriber).

任何想法我怎么能做到这一点?我缺少的任何其他表中是否有用户数据?订阅者没有任何与其关联的帖子.

Any ideas how I can do this? Are there users data in any other tables that I'm missing? Subscribers do not have any posts associated with them.

我见过一些垃圾邮件注册插件，但它们是预防性的.现在，我需要一种方法来摆脱这些烦人的垃圾邮件发送者的电子邮件.

I've seen some plugins for spam sign ups but they are preventative. Right now, I need a way to get rid of these annoying spammers emails.

推荐答案

您可以使用 INNER JOIN 使用 DELETE 时在 MySQL 中.

You can use a INNER JOIN when using DELETE in MySQL.

DELETE
FROM  wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE  wp_users.user_email LIKE  "%@hotmail%" AND [etc, etc.]

这个解决方案给你带来了两个(或者更多;-))问题:1)你不能重新分配帖子和链接(如果你愿意)和 2)你必须在 MySQL 中处理 JSON 值.

This solution gives you two (or maybe more ;-)) problems: 1) you can't reassign posts and links (if you want) and 2) you have to deal with JSON values in MySQL.

更好的方法是使用 WordPress 功能来完成这项工作.您可以使用 get_users 来搜索用户和 wp_delete_user 删除用户.

A better way is to use WordPress functions for the job. You can use get_users to search for users and wp_delete_user to delete the user.

<?php $args = array(
    'blog_id'      => $GLOBALS['blog_id'],
    'role'         => 'subscriber',
    'search'       => '*@hotmail.com'
 );

$blogusers = get_users($args);
    foreach ($blogusers as $user) {
        wp_delete_user( $user->ID, $reassign );
    }
?>

请阅读函数参考:它将帮助您解决这个问题和未来的问题.

Please read the Function Reference: it will help you solve this problem and future problems.

这篇关于批量删除垃圾邮件 Wordpress 用户的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！