Remove Magento 2 spam customer accounts quickly in database

Question

I have thousands of spam accounts in the customer database. Is there a way to quickly remove these?

I cannot do it quickly in admin panel, as it will crash the site if I try to delete them all at once. If I only delete 200 at a time it will take a very long time.

Is there a query I can run in SQL/PhpMyAdmin? All the spam accounts have the qq.com email address.

Answer 1

Try below SQL Query:

DELETE FROM customer_entity WHERE email LIKE '%qq.com';

I hope it helps...!!!

Answer 2

First thing you need to do if you want to programmatically delete the customers, is identify a common pattern with the accounts (e.g emails all end in provider.ru)

Next, you will want to backup your database in case anything goes wrong. And ideally, test this on a DEV/UAT site.

Following code examples are untested but should put you in the right direction, verify them yourself on a dev/uat environment and take proper backups beforehand.

---

SQL

After that, you can execute a select on the customer_entity and confirm all the accounts are spam ones, then run a delete with the same conditions

SELECT * FROM customer_entity WHERE email LIKE '%provider.ru';
DELETE FROM customer_entity WHERE email LIKE '%provider.ru';

---

Mage 2 Script

A script version of the above would be something like the following, uploading into the root of the site and executing from cli with php tends to work best

<?php

ini_set('display_errors', 1);
ini_set('max_execution_time', 0);
ini_set("memory_limit", "-1");
set_time_limit(0);
error_reporting(E_ALL);

require './app/bootstrap.php';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('admin');

$customerFactory = $objectManager->create(\Magento\Customer\Model\CustomerFactory::class);
$customers = $customerFactory->create()->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('email', array('like' => '%provider.ru'));

foreach ($customers as $customer) {
    echo "Found Customer: {$customer->getId()} :: {$customer->getEmail()}",PHP_EOL;
    // $customer->delete();
}

Answer 3

A note in my case, removing the customer spam accounts did not appear to be enough. As the quote table still linked to them. When new orders/customer are made, the customers name was getting replaced with the spam details. Had over 5000 to remove.

Answer 4

This one works:

DELETE FROM customer_entity WHERE email LIKE '%qq.com';

In order to take effect in the admin, you need to reindex:

php bin/magento indexer:reindex