使用Magento，在正式上线前或上线后，我们都会做一些测试订单并注册账号来确定各项功能是否均正常，但通常又不希望在正式环境中保存这些数据，这时就需要将这些注册数据和订单数据进行删除。其中一种方法是直接对数据库进行操作，由于在Magento中将涉及较多的表格，以下列举中相应的SQL语句，如果您的数据库中使用了自定义前缀，请自行添加：
SET FOREIGN_KEY_CHECKS=0; -- Here's where we reset the orders TRUNCATE <code>sales_flat_order</code>; TRUNCATE <code>sales_flat_order_address</code>; TRUNCATE <code>sales_flat_order_grid</code>; TRUNCATE <code>sales_flat_order_item</code>; TRUNCATE <code>sales_flat_order_status_history</code>; TRUNCATE <code>sales_flat_quote</code>; TRUNCATE <code>sales_flat_quote_address</code>; TRUNCATE <code>sales_flat_quote_address_item</code>; TRUNCATE <code>sales_flat_quote_item</code>; TRUNCATE <code>sales_flat_quote_item_option</code>; TRUNCATE <code>sales_flat_order_payment</code>; TRUNCATE <code>sales_flat_quote_payment</code>; TRUNCATE <code>sales_flat_shipment</code>; TRUNCATE <code>sales_flat_shipment_item</code>; TRUNCATE <code>sales_flat_shipment_grid</code>; TRUNCATE <code>sales_flat_invoice</code>; TRUNCATE <code>sales_flat_invoice_grid</code>; TRUNCATE <code>sales_flat_invoice_item</code>; TRUNCATE <code>sendfriend_log</code>; TRUNCATE <code>tag</code>; TRUNCATE <code>tag_relation</code>; TRUNCATE <code>tag_summary</code>; TRUNCATE <code>wishlist</code>; TRUNCATE <code>log_quote</code>; TRUNCATE <code>report_event</code>; ALTER TABLE <code>sales_flat_order</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_order_address</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_order_grid</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_order_item</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_order_status_history</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote_address</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote_address_item</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote_item</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote_item_option</code> AUTO_INCREMENT=1; ALTER TABLE <code>sendfriend_log</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_order_payment</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_quote_payment</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_shipment</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_shipment_item</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_invoice</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_invoice_grid</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_invoice_item</code> AUTO_INCREMENT=1; ALTER TABLE <code>sales_flat_shipment_grid</code> AUTO_INCREMENT=1; ALTER TABLE <code>tag</code> AUTO_INCREMENT=1; ALTER TABLE <code>tag_relation</code> AUTO_INCREMENT=1; ALTER TABLE <code>tag_summary</code> AUTO_INCREMENT=1; ALTER TABLE <code>wishlist</code> AUTO_INCREMENT=1; ALTER TABLE <code>log_quote</code> AUTO_INCREMENT=1; ALTER TABLE <code>report_event</code> AUTO_INCREMENT=1; -- Here's where we reset the customers TRUNCATE <code>customer_address_entity</code>; TRUNCATE <code>customer_address_entity_datetime</code>; TRUNCATE <code>customer_address_entity_decimal</code>; TRUNCATE <code>customer_address_entity_int</code>; TRUNCATE <code>customer_address_entity_text</code>; TRUNCATE <code>customer_address_entity_varchar</code>; TRUNCATE <code>customer_entity</code>; TRUNCATE <code>customer_entity_datetime</code>; TRUNCATE <code>customer_entity_decimal</code>; TRUNCATE <code>customer_entity_int</code>; TRUNCATE <code>customer_entity_text</code>; TRUNCATE <code>customer_entity_varchar</code>; TRUNCATE <code>log_customer</code>; TRUNCATE <code>log_visitor</code>; TRUNCATE <code>log_visitor_info</code>; ALTER TABLE <code>customer_address_entity</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_address_entity_datetime</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_address_entity_decimal</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_address_entity_int</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_address_entity_text</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_address_entity_varchar</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity_datetime</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity_decimal</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity_int</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity_text</code> AUTO_INCREMENT=1; ALTER TABLE <code>customer_entity_varchar</code> AUTO_INCREMENT=1; ALTER TABLE <code>log_customer</code> AUTO_INCREMENT=1; ALTER TABLE <code>log_visitor</code> AUTO_INCREMENT=1; ALTER TABLE <code>log_visitor_info</code> AUTO_INCREMENT=1; -- This is to Reset all the ID counters TRUNCATE <code>eav_entity_store</code>; ALTER TABLE <code>eav_entity_store</code> AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1;
另一个更为简便的方法是采用Inchoo上已经写好的一套php代码，将代码保存到文件中并上传到Magento所在的根目录下执行程序即可
/**
* @author Dejan Radic <dejan.radic@inchoo.net>
*/
if (version_compare(phpversion(), '5.2.0', '<')===true) {
echo '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
exit;
}
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
$mageFilename = 'app/Mage.php';
if (!file_exists($mageFilename)) {
echo $mageFilename." was not found";
exit;
}
require_once $mageFilename;
Mage::app();
$executionPath = null;
/*
* determine Magento Edition
*/
if (file_exists('LICENSE_EE.txt')) {
$edition = 'EE';
}elseif (file_exists('LICENSE_PRO.html')) {
$edition = 'PE';
} else {
$edition = 'CE';
}
if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
|| ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
|| ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
){
$executionPath = 'old';
} else {
$executionPath = 'new';
}
$xpathEntity = 'global/models/sales_entity/entities//table';
if ($executionPath == 'old') {
$xpathResource = 'global/models/sales_mysql4/entities//table';
} else {
$xpathResource = 'global/models/sales_resource/entities//table';
}
$salesEntitiesConf = array_merge(
Mage::getSingleton('core/config')->init()->getXpath($xpathEntity),
Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
);
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_write');
/*
* If you want delete System/Order Statuses (Status and State) you
* should comments below lines (46-51)
*/
$skipTables = array (
$resource->getTableName('sales_order_status'),
$resource->getTableName('sales_order_status_state'),
$resource->getTableName('sales_order_status_label')
);
$salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);
/*
Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf
2.2. Adapters:
... The new Varien_DB_Adapter_Interface was added to sign a contract that all
developed adapters must execute in order to get Magento working on an actual
database. The interface describes the list of methods and constants that can be used by resource models...
Used below in the loop:
* If $executionPath == 'old'
* Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
* Varien_Db_Adapter_Pdo_Mysql::truncate()
* Else
* Varien_Db_Adapter_Interface::isTableExists()
* Varien_Db_Adapter_Interface::truncateTable()
*/
while ($table = current($salesEntitiesConf) ){
$table = $resource->getTableName($table);
if ($executionPath == 'old') {
$isTableExists = $connection->showTableStatus($table);
} else {
$isTableExists = $connection->isTableExists($table);
}
if ($isTableExists) {
try {
if ($executionPath == 'old') {
$connection->truncate($table);
} else {
$connection->truncateTable($table);
}
printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
} catch(Exception $e) {
printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
}
}
next($salesEntitiesConf);
}
exit('All done...');
