清空magento所有的产品数据 Delete magento category / products by SQL

最新推荐文章于 2021-01-30 18:29:50 发布

原创最新推荐文章于 2021-01-30 18:29:50 发布 · 292 阅读

0 ·

CC 4.0 BY-SA版权

WP / Joomla! / Magento / Shopify / Drupal / Moodle / Zimbra 专栏收录该内容

275 篇文章

订阅专栏

本文提供了一套适用于 Magento 1.5 至 1.7 版本的 SQL 脚本，用于清除产品、类别及客户数据，并将数据库重置为初始状态。通过禁用外键约束，确保了数据删除过程的顺利进行。

Magento 1.5 and 1.6

Truncate products

This should remove all the products and their related records.

TRUNCATE TABLE `catalog_product_bundle_option`;
    TRUNCATE TABLE `catalog_product_bundle_option_value`;
    TRUNCATE TABLE `catalog_product_bundle_selection`;
    TRUNCATE TABLE `catalog_product_entity_datetime`;
    TRUNCATE TABLE `catalog_product_entity_decimal`;
    TRUNCATE TABLE `catalog_product_entity_gallery`;
    TRUNCATE TABLE `catalog_product_entity_int`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
    TRUNCATE TABLE `catalog_product_entity_text`;
    TRUNCATE TABLE `catalog_product_entity_tier_price`;
    TRUNCATE TABLE `catalog_product_entity_varchar`;
    TRUNCATE TABLE `catalog_product_link`;
    TRUNCATE TABLE `catalog_product_link_attribute`;
    TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
    TRUNCATE TABLE `catalog_product_link_attribute_int`;
    TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
    TRUNCATE TABLE `catalog_product_link_type`;
    TRUNCATE TABLE `catalog_product_option`;
    TRUNCATE TABLE `catalog_product_option_price`;
    TRUNCATE TABLE `catalog_product_option_title`;
    TRUNCATE TABLE `catalog_product_option_type_price`;
    TRUNCATE TABLE `catalog_product_option_type_title`;
    TRUNCATE TABLE `catalog_product_option_type_value`;
    TRUNCATE TABLE `catalog_product_super_attribute`;
    TRUNCATE TABLE `catalog_product_super_attribute_label`;
    TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
    TRUNCATE TABLE `catalog_product_super_link`;
    TRUNCATE TABLE `catalog_product_enabled_index`;
    TRUNCATE TABLE `catalog_product_website`;
    TRUNCATE TABLE `catalog_product_entity`;
     
    TRUNCATE TABLE `cataloginventory_stock`;
    TRUNCATE TABLE `cataloginventory_stock_item`;
    TRUNCATE TABLE `cataloginventory_stock_status`;
     
    INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
    INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
    INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');

n magento 1.5 you need to add in cataloginventory_stock., I added a record with stock_id 1 and stock_name to Default. Without this there will be a foreign key constraint fail, see http://www.magentocommerce.com/boards/v/viewthread/41764/#t169087 for further explanation

Truncate categories

This should remove all the categories and their related records. It will also create new Root category.

TRUNCATE TABLE `catalog_category_entity`;
    TRUNCATE TABLE `catalog_category_entity_datetime`;
    TRUNCATE TABLE `catalog_category_entity_decimal`;
    TRUNCATE TABLE `catalog_category_entity_int`;
    TRUNCATE TABLE `catalog_category_entity_text`;
    TRUNCATE TABLE `catalog_category_entity_varchar`;
    TRUNCATE TABLE `catalog_category_product`;
    TRUNCATE TABLE `catalog_category_product_index`;
     
    INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);
    INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,32,1,2,1);
    INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');
     
    /**** Magento ver. 1.6.x.x ****/
     
    TRUNCATE TABLE `catalog_category_entity`;
    TRUNCATE TABLE `catalog_category_entity_datetime`;
    TRUNCATE TABLE `catalog_category_entity_decimal`;
    TRUNCATE TABLE `catalog_category_entity_int`;
    TRUNCATE TABLE `catalog_category_entity_text`;
    TRUNCATE TABLE `catalog_category_entity_varchar`;
    TRUNCATE TABLE `catalog_category_product`;
    TRUNCATE TABLE `catalog_category_product_index`;
     
    INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','0000-00-00 00:00:00','1',1,0,1), (2,3,3,1,'0000-00-00 00:00:00','0000-00-00 00:00:00','1/2','1','1','0');
    INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,36,0,2,1),(3,3,61,0,2,1),(4,3,44,0,2,NULL),(5,3,45,0,2,1),(6,3,62,0,2,1),(7,3,63,0,2,1),(8,3,64,0,2,NULL);
    INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,35,0,2,'Default Category'),(3,3,37,0,2,'default-category'),(4,3,40,0,2,NULL),(5,3,43,0,2,'PRODUCTS'),(6,3,52,0,2,NULL),(7,3,55,0,2,NULL);
     
    /**** Magento ver. 1.6.x.x ****/

Truncate customers

This should remove all the customers and their related records.

    TRUNCATE TABLE `customer_address_entity`;
    TRUNCATE TABLE `customer_address_entity_datetime`;
    TRUNCATE TABLE `customer_address_entity_decimal`;
    TRUNCATE TABLE `customer_address_entity_int`;
    TRUNCATE TABLE `customer_address_entity_text`;
    TRUNCATE TABLE `customer_address_entity_varchar`;
    TRUNCATE TABLE `customer_entity`;
    TRUNCATE TABLE `customer_entity_datetime`;
    TRUNCATE TABLE `customer_entity_decimal`;
    TRUNCATE TABLE `customer_entity_int`;
    TRUNCATE TABLE `customer_entity_text`;
    TRUNCATE TABLE `customer_entity_varchar`;

Magento 1.7

I was writing a bulk category/product import script for magento 1.7. For debugging I had to frequently clear all the erroneous data inserted while coding and run my script again and again. Searching for an easy solution I came into this magento wiki that has the queries to delete all the products and categories for Magento 1.5 and 1.6. In 1.7 the attribute ids are not same so I needed an updated one. So here is the queries for 1.7 to reset your product / category tables to the initial position just after fresh magento installation.

Please note the first and last sql commands that are used to ignore the foreign key constraints. Without these 2 lines, you will get error.

    SET FOREIGN_KEY_CHECKS = 0;

    TRUNCATE TABLE `catalog_product_bundle_option`;
    TRUNCATE TABLE `catalog_product_bundle_option_value`;
    TRUNCATE TABLE `catalog_product_bundle_selection`;
    TRUNCATE TABLE `catalog_product_entity_datetime`;
    TRUNCATE TABLE `catalog_product_entity_decimal`;
    TRUNCATE TABLE `catalog_product_entity_gallery`;
    TRUNCATE TABLE `catalog_product_entity_int`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery`;
    TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
    TRUNCATE TABLE `catalog_product_entity_text`;
    TRUNCATE TABLE `catalog_product_entity_tier_price`;
    TRUNCATE TABLE `catalog_product_entity_varchar`;
    TRUNCATE TABLE `catalog_product_link`;
    TRUNCATE TABLE `catalog_product_link_attribute`;
    TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
    TRUNCATE TABLE `catalog_product_link_attribute_int`;
    TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
    TRUNCATE TABLE `catalog_product_link_type`;
    TRUNCATE TABLE `catalog_product_option`;
    TRUNCATE TABLE `catalog_product_option_price`;
    TRUNCATE TABLE `catalog_product_option_title`;
    TRUNCATE TABLE `catalog_product_option_type_price`;
    TRUNCATE TABLE `catalog_product_option_type_title`;
    TRUNCATE TABLE `catalog_product_option_type_value`;
    TRUNCATE TABLE `catalog_product_super_attribute`;
    TRUNCATE TABLE `catalog_product_super_attribute_label`;
    TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
    TRUNCATE TABLE `catalog_product_super_link`;
    TRUNCATE TABLE `catalog_product_enabled_index`;
    TRUNCATE TABLE `catalog_product_website`;
    TRUNCATE TABLE `catalog_product_entity`;

    TRUNCATE TABLE `cataloginventory_stock`;
    TRUNCATE TABLE `cataloginventory_stock_item`;
    TRUNCATE TABLE `cataloginventory_stock_status`;

    TRUNCATE TABLE `catalog_category_entity`;
    TRUNCATE TABLE `catalog_category_entity_datetime`;
    TRUNCATE TABLE `catalog_category_entity_decimal`;
    TRUNCATE TABLE `catalog_category_entity_int`;
    TRUNCATE TABLE `catalog_category_entity_text`;
    TRUNCATE TABLE `catalog_category_entity_varchar`;
    TRUNCATE TABLE `catalog_category_product`;
    TRUNCATE TABLE `catalog_category_product_index`;

    INSERT INTO `catalog_category_entity` (`entity_id`, `entity_type_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES (1, 3, 0, 0, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1′, 0, 0, 1), (2, 3, 3, 1, ’2012-06-17 22:20:47′, ’2012-06-17 22:20:47′, ’1/2′, 1, 1, 0);
    INSERT INTO `catalog_category_entity_int` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 67, 0, 1, 1), (2, 3, 67, 1, 1, 1), (3, 3, 42, 0, 2, 1), (4, 3, 67, 0, 2, 1), (5, 3, 42, 1, 2, 1), (6, 3, 67, 1, 2, 1);
    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (1, 3, 41, 0, 1, ‘Root Catalog’), (2, 3, 41, 1, 1, ‘Root Catalog’), (3, 3, 43, 1, 1, ‘root-catalog’), (4, 3, 41, 0, 2, ‘Default Category’), (5, 3, 41, 1, 2, ‘Default Category’), (6, 3, 49, 1, 2, ‘PRODUCTS’), (7, 3, 43, 1, 2, ‘default-category’);

    INSERT INTO `catalog_product_link_type` (`link_type_id`, `code`) VALUES (1, ‘relation’), (3, ‘super’), (4, ‘up_sell’), (5, ‘cross_sell’);
    INSERT INTO `catalog_product_link_attribute` (`product_link_attribute_id`, `link_type_id`, `product_link_attribute_code`, `data_type`) VALUES (1, 1, ‘position’, ‘int’), (2, 3, ‘position’, ‘int’), (3, 3, ‘qty’, ‘decimal’), (4, 4, ‘position’, ‘int’), (5, 5, ‘position’, ‘int’);
    INSERT INTO `cataloginventory_stock` (`stock_id`, `stock_name`) VALUES (1, ‘Default’);

    SET FOREIGN_KEY_CHECKS = 1;

from: http://www.imranulhoque.com/magento/reset-delete-magento-category-products-by-sql-for-magento-version-1-7/