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.
Try below SQL Query:
DELETE FROM customer_entity WHERE email LIKE '%qq.com'; I hope it helps...!!!
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.
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'; 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(); } 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.
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 