database 如何删除 Magento 中的所有目录产品

How to remove all catalog products in Magento

提问by Diego Favero

I've trying to import a products list to magento. In the firsts test, I got success, but the products were not showing up in back or front office.

我正在尝试将产品列表导入 magento。在第一次测试中，我成功了，但产品没有出现在后台或前台。

After redo the import process few times, I found out that I have the imported products on database, but, still not showing.

重做几次导入过程后，我发现我在数据库中有导入的产品，但仍然没有显示。

If I access de Catalog > Manage Categories > Default Category -> Category Products I may see the imported products.

如果我访问 de Catalog > Manage Categories > Default Category -> Category Products 我可能会看到导入的产品。

Later on, I found a error in my import file ... and I may correctly import products

后来，我发现我的导入文件有错误……我可以正确导入产品

instead .... I have found conflicts in the 'sku' field ...

相反......我发现'sku'字段中存在冲突......

those first products are not showing up yet, but they are still on database...

那些第一批产品还没有出现，但它们仍在数据库中......

Question: How do I remove those products if I can't access them ???? May I do that direct on database ? deleting the rows can generate other issues ?

问题：如果我无法访问这些产品，我该如何删除它们？？？？我可以直接在数据库上做吗？删除行会产生其他问题吗？

any clue will be appreciated !

任何线索将不胜感激！

Magento ver. 1.7.0.1

Magento 版。1.7.0.1

回答by Kalpesh

Reset all product tables. Beware, below script will delete ALL your product data so do it carefully.

重置所有产品表。请注意，以下脚本将删除您的所有产品数据，因此请谨慎操作。

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_product_link`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_entity`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `catalog_category_product`;
DELETE FROM catalog_product_flat_1;
DELETE FROM catalog_product_flat_10;
DELETE FROM catalog_product_flat_11;
DELETE FROM catalog_product_flat_12;
DELETE FROM catalog_product_flat_13;
DELETE FROM catalog_product_flat_14;
DELETE FROM catalog_product_flat_15;
DELETE FROM catalog_product_flat_16;
DELETE FROM catalog_product_flat_17;
DELETE FROM catalog_product_flat_18;
DELETE FROM catalog_product_flat_19;
DELETE FROM catalog_product_flat_2;
DELETE FROM catalog_product_flat_20;
DELETE FROM catalog_product_flat_21;
DELETE FROM catalog_product_flat_22;
DELETE FROM catalog_product_flat_23;
DELETE FROM catalog_product_flat_24;
DELETE FROM catalog_product_flat_25;
DELETE FROM catalog_product_flat_26;
DELETE FROM catalog_product_flat_27;
DELETE FROM catalog_product_flat_28;
DELETE FROM catalog_product_flat_29;
DELETE FROM catalog_product_flat_3;
DELETE FROM catalog_product_flat_30;
DELETE FROM catalog_product_flat_31;
DELETE FROM catalog_product_flat_32;
DELETE FROM catalog_product_flat_33;
DELETE FROM catalog_product_flat_34;
DELETE FROM catalog_product_flat_35;
DELETE FROM catalog_product_flat_36;
DELETE FROM catalog_product_flat_37;
DELETE FROM catalog_product_flat_4;
DELETE FROM catalog_product_flat_5;
DELETE FROM catalog_product_flat_6;
DELETE FROM catalog_product_flat_7;
DELETE FROM catalog_product_flat_8;
DELETE FROM catalog_product_flat_9;
SET FOREIGN_KEY_CHECKS = 1;

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');

You may require re-indexing all your indexes after running above query.

运行上述查询后，您可能需要重新索引所有索引。

System > Index Management > Reindex all

System > Index Management > Reindex all

http://ka.lpe.sh/2012/08/09/magento-how-to-delete-remove-all-products-from-all-categories/

http://ka.lpe.sh/2012/08/09/magento-how-to-delete-remove-all-products-from-all-categories/

Have fun!

玩得开心！

回答by krishna singh

Delete products with a SQL query is not the Magento way and I recommend not doing it. It may cause database corruption or problems with the foreign key indexes/constraints as stated here.

使用 SQL 查询删除产品不是 Magento 的方式，我建议不要这样做。它可能会导致数据库损坏或外键索引/约束出现问题，如此处所述。

Everybody interested in using a correct, safe and also fast way to alter things in Magento should ALWAYS use Magento's ORM functions.

每个有兴趣在 Magento 中使用正确、安全且快速的方法来改变事物的人都应该始终使用 Magento 的 ORM 函数。

In this case this would be in a php script.

在这种情况下，这将在 php 脚本中。

Mage::getModel('catalog/product')->getCollection()->delete();

This is the only safe way in Magento.

这是 Magento 中唯一安全的方式。

回答by Kinjalkumar Prajapati

You can delete all the product using Direct Sql.

您可以使用 Direct Sql 删除所有产品。

Please take backup your database, and run the following sql queries.

请备份您的数据库，并运行以下 sql 查询。

Reset all product tables. Beware, below script will delete ALL your product data so do it carefully.

重置所有产品表。请注意，以下脚本将删除您的所有产品数据，因此请谨慎操作。

`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`;
INSERT INTO `catalog_product_link_type` VALUES(1, 'relation');
INSERT INTO `catalog_product_link_type` VALUES(3, 'super');
INSERT INTO `catalog_product_link_type` VALUES(4, 'up_sell');
INSERT INTO `catalog_product_link_type` VALUES(5, 'cross_sell');
INSERT INTO `catalog_product_link_attribute` VALUES(1, 1, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(2, 3, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(3, 3, 'qty', 'decimal');
INSERT INTO `catalog_product_link_attribute` VALUES(4, 4, 'position', 'int');
INSERT INTO `catalog_product_link_attribute` VALUES(5, 5, 'position', 'int');
INSERT INTO `cataloginventory_stock` VALUES(1, 'Default');
SET FOREIGN_KEY_CHECKS = 1;`

**Remove the Product counts from category at back-end**

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
SET FOREIGN_KEY_CHECKS = 1;`


**Remove the Bestseller and Most view Products at Dashboards**

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `report_compared_product_index`;
TRUNCATE TABLE `report_viewed_product_aggregated_daily`;
TRUNCATE TABLE `report_viewed_product_aggregated_monthly`;
TRUNCATE TABLE `report_viewed_product_aggregated_yearly`;
TRUNCATE TABLE `report_viewed_product_index`;
TRUNCATE TABLE `sales_bestsellers_aggregated_daily`;
TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`;
TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`;
SET FOREIGN_KEY_CHECKS = 1;`

**Flush all the flat table of products**

`SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM catalog_product_flat_1;
DELETE FROM catalog_product_flat_2;
DELETE FROM catalog_product_flat_3;
DELETE FROM catalog_product_flat_4;
DELETE FROM catalog_product_flat_5;
SET FOREIGN_KEY_CHECKS = 1;`

**Remove the review and rating of products**

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table `rating_option_vote`; 
TRUNCATE table `rating_option_vote_aggregated`;
TRUNCATE table `review`; 
TRUNCATE table `review_detail`; 
TRUNCATE table `review_entity_summary`; 
TRUNCATE table `review_store`;
SET FOREIGN_KEY_CHECKS = 1;`

**For enterprise flush the following tables**

`SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_flat_cl`;
TRUNCATE TABLE `catalog_category_product_cat_cl`;
TRUNCATE TABLE `catalog_category_product_index_cl`;
TRUNCATE TABLE `catalog_product_flat_cl`;
TRUNCATE TABLE `catalog_product_index_price_cl`;
TRUNCATE TABLE `cataloginventory_stock_status_cl`;
TRUNCATE TABLE `catalogsearch_fulltext_cl`;
SET FOREIGN_KEY_CHECKS = 1;`

回答by Oliver Ernst

With your SQL-Sql Script i broke my price index.This happens because we use group prices in the shop. Following error occurred every timewhen i tried to reindex the index "catalog_product_price":

用你的 SQL-Sql 脚本，我打破了我的价格指数。发生这种情况是因为我们在商店中使用团体价格。每次尝试重新索引索引“catalog_product_price”时都会发生以下错误：

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails     (`db`.`catalog_product_index_group_price`, CONSTRAINT `FK_CAT_PRD_IDX_GROUP_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`), query was: INSERT INTO `catalog_product_index_group_price` SELECT `gp`.`entity_id`, `cg`.`customer_group_id`, `cw`.`website_id`, MIN(IF(gp.website_id = 0, ROUND(gp.value * cwd.rate, 4), gp.value)) FROM `catalog_product_entity_group_price` AS `gp`
 INNER JOIN `customer_group` AS `cg` ON gp.all_groups = 1 OR (gp.all_groups = 0 AND gp.customer_group_id = cg.customer_group_id)
 INNER JOIN `core_website` AS `cw` ON gp.website_id = 0 OR gp.website_id = cw.website_id
 INNER JOIN `catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id WHERE (cw.website_id != 0) GROUP BY `gp`.`entity_id`,
        `cg`.`customer_group_id`,
        `cw`.`website_id` ON DUPLICATE KEY UPDATE `price` = VALUES(`price`)

Please add following line:

请添加以下行：

TRUNCATE TABLE `catalog_product_entity_group_price`;

回答by Hamid Neotel

You also need to add:

您还需要添加：

TRUNCATE TABLE catalog_product_entity;

Otherwise you will get integrity constraint errors.

否则你会得到完整性约束错误。

回答by Elaman

The answer given by Kalpesh Mehta works flawlessly.

Kalpesh Mehta 给出的答案完美无缺。

But after you remove all products, import bunch of new prodcuts and try to delete it using admin UI you could an error like this:

但是在删除所有产品后，导入一堆新产品并尝试使用管理 UI 删除它，您可能会出现如下错误：

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(magento_store.q.items_count- 1)'

SQLSTATE[22003]：数值超出范围：1690 BIGINT UNSIGNED 值超出范围在 '( magento_store. q. items_count- 1)'

So, this article helped me a lot: Magento deleting product BIGINT UNSIGNED value is out of range

所以，这篇文章对我帮助很大：Magento 删除产品 BIGINT UNSIGNED value is out of range

It provides several options to solve problem and the first one worked in my case:

它提供了几种解决问题的选项，第一个在我的情况下有效：

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 30 DAY);