I don't know (yet) an sql command to get you what you need, mainly because the customer entity is EAV and the firstname, lastname and e-mail are kept in a separate table on separate line, but here is what you can do to identify the ids that need to be deleted.
First and most important...backup your database in case I'm wrong.
Then, and only then, you can create a small script. Create the file cleanup.php
located in the same folder as index.php
.
<?php
error_reporting(E_ALL | E_STRICT);
define('MAGENTO_ROOT', getcwd());
$mageFilename = MAGENTO_ROOT . '/app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
//instantiate the app model
Mage::app();
$keyAttributes = array('firstname', 'lastname', 'email');
$collection = Mage::getModel('customer/customer')->getCollection()
->addAttributeToSelect($keyAttributes)
->addAttributeToSort('entity_id', 'ASC');
$existingKeys = array();
$idsToDelete = array();
$glue = '####';
foreach ($collection as $customer) {
$keys = array();
//build an array with the fname, lname and email
foreach ($keyAttributes as $attribute) {
$keys[] = $customer->getData($attribute);
}
//implode all the values in a single string
$key = implode($glue, $keys); // variable spelling is changed to keys
//if this string was not found in the past, mark it as found
if (!isset($existingKeys[$key])) {
$existingKeys[$key] = 1;
} else { //otherwise, the customer needs to be deleted
$idsToDelete[] = $customer->getId();
}
}
//generate some sql queries that need to be executed
//delete 50 at a time
$chunkSize = 50;
foreach (array_chunk($idsToDelete, $chunkSize) as $chunk) {
echo "DELETE FROM customer_entity WHERE entity_id IN (".implode(',', $chunk).");<br />";
}
Now you can call in the browser magento_root/cleanup.php
and you should see a set of delete statements.
You can execute them on your db.
These will delete the records from customer_entity
table, and the rest of the values associated to those customers will cascade nicely.
Note: the code is not tested but this is how I would do it.
Customer delete operation is valid from admin only. When you perform delete operation, magento check isSecureArea is true/false. So you can assign isSecureArea by following way:
/**
* @var \Magento\Framework\Registry
*/
protected $registry;
/**
* @param Context $context
* @param PageFactory $resultPageFactory
*/
public function __construct(
\Magento\Framework\Registry $registry
) {
$this->registry = $registry;
}
Now change your code following way:
$this->registry->register('isSecureArea', true);
$customer = $this->customerFactory->create()->load($id);
$customer->delete();
Clear cache.
Best Answer
If you want to delete the customer with some code that runs from the admin area, this should be enough:
If you want to delete the customer from the frontend area you need to wrap the code above in this: