magento中删除清空指定目录或属性集下产品的自定义选项的sql脚本

magento的批量上传,可以给产品的增加自定义选项,但修改不了已存在的自定义选项内容,这时清空,并将完成的自定义选项重新上传,是一个比较好的实现方式!
以下是magento中删除指定目录下产品的自定义选项的sql脚本,其中182是指定的目录id,大家在用时做一个替换。

delete from catalog_product_option_type_price where option_type_id in  (select option_type_id from  catalog_product_option_type_value  where option_id in (select option_id  from catalog_product_option where product_id   in (SELECT product_id FROM `catalog_category_product` WHERE category_id =182)));
delete from catalog_product_option_type_title  where option_type_id in  (select option_type_id from  catalog_product_option_type_value   where option_id in (select option_id  from catalog_product_option where product_id   in (SELECT product_id FROM `catalog_category_product`  WHERE category_id =182)));
delete from  catalog_product_option_type_value  where option_id in (select option_id  from catalog_product_option where product_id   in  (SELECT product_id FROM `catalog_category_product` WHERE category_id =182));
delete from catalog_product_option_price  where  option_id in (select option_id  from catalog_product_option where product_id   in (SELECT product_id FROM `catalog_category_product` WHERE category_id =182));
delete  from catalog_product_option_title  where  option_id in (select option_id  from catalog_product_option where product_id   in (SELECT product_id FROM `catalog_category_product` WHERE category_id =182));
delete  from catalog_product_option where product_id   in (SELECT product_id FROM `catalog_category_product` WHERE category_id =182);

删除指定属性集的产品自定义选项的sql脚本,其中11是属性集id,用时做个替换

delete from catalog_product_option_type_price where option_type_id in  (select option_type_id from  catalog_product_option_type_value  where option_id in (select option_id  from  catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11)));
delete from catalog_product_option_type_title  where option_type_id in  (select option_type_id from  catalog_product_option_type_value  where option_id in (select option_id  from catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11)));
delete from  catalog_product_option_type_value  where option_id in (select option_id  from catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11));
delete from catalog_product_option_price  where  option_id in (select option_id  from catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11));
delete  from catalog_product_option_title  where  option_id in (select option_id  from catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11));
delete  from catalog_product_option where product_id   in (SELECT entity_id FROM `catalog_product_entity` WHERE attribute_set_id =11);

Magento常用的SQL命令集合

以下所有命令仅供参考,不同版本,不同时间,可能数据略有不同,慎用!

1. 导出导入 Magento 所有分类和产品
    分类和产品是存放在以 catalog 开头的所有表中,对这组表进行导出导入即可实现此功能。
    导入分类产品的 SQL 文件前注意:
    在首行加入:SET FOREIGN_KEY_CHECKS=0;
    在末行加入:SET FOREIGN_KEY_CHECKS=1;
    原因是 Magento 使用 Innodb 存储引擎。

2. 批量修改分类的 Display Settings ——> Is Anchor 值为 No
    UPDATE `catalog_category_entity_int` set value=0 WHERE value=1 AND attribute_id=120;

3. 批量处理所有 exclude 状态的图片
    UPDATE `catalog_product_entity_media_gallery_value` SET disabled=0 WHERE disabled=1;

4. 批量调整所有产品的价格 ( 参考命令在 1.3 下通过 )
    UPDATE `catalog_product_entity_decimal` SET value=round(value*1.45) WHERE attribute_id=99;
    执行完后,需要到缓存管理里刷新:Layered Navigation Indices ,即可同步数据库里的关联表。

5. 清空邮件队列
    TRUNCATE TABLE `newsletter_queue`;
    TRUNCATE TABLE `newsletter_queue_link`;
    TRUNCATE TABLE `newsletter_queue_store_link`;
    注意:同时向上万顾客发邮件时,不要在后台查看邮件队列,不然服务器压力会很大,待邮件发完之后,记着清空邮件队列,这样在后台进入邮件队列就不会大量消耗服务器资源了。

6. Magento 转移站后,经常会出现下面这个提示,运行一下下面的SQL命令即可恢复正常。
    错误提示: Notice: Undefined index: 0 app/code/core/Mage/Core/Model/Mysql4/Config.php on line 92
    SET FOREIGN_KEY_CHECKS=0;
    update `core_store` set store_id = 0 where code='admin';
    update `core_store_group` set group_id = 0 where name='Default';
    update `core_website` set website_id = 0 where code='admin';
    update `customer_group` set customer_group_id = 0 where customer_group_code='NOT LOGGED IN';
    SET FOREIGN_KEY_CHECKS=1;
    不过要明白,这个错误是使用了第三方数据库备份工具导致的,Magento 自带的备份功能是不会出现这个错误的。

7. 根据产品的 SKU 批量将产品自定义选项设为非必填: 
    UPDATE `catalog_product_option` SET is_require=0 WHERE product_id IN (SELECT entity_id FROM `catalog_product_entity` WHERE sku LIKE 'SKU %');

8. 关闭/开启 所有缺货产品
    SET FOREIGN_KEY_CHECKS=0;
    UPDATE `catalog_product_entity_int` SET value=2 WHERE attribute_id=80 and entity_id IN (SELECT product_id FROM `cataloginventory_stock_status` WHERE stock_status=0);
    SET FOREIGN_KEY_CHECKS=1;
    其它说明:value=2 为关闭,1为开启,attribute_id 对应不同版本的产品禁用项,最后执行完命令需要重建分类索引。

9. 取消所有问题邮件订阅
    UPDATE `newsletter_subscriber` SET subscriber_status=3 WHERE subscriber_id IN (SELECT subscriber_id FROM `newsletter_problem`);

10. 清除产品与分类的描述与 Meta
       重置所有产品short description
       UPDATE `catalog_product_entity_text` SET value='Short Description' WHERE attribute_id=506;
       清除所有产品Meta
       UPDATE `catalog_product_entity_text` SET value='' WHERE attribute_id=97 OR attribute_id=104;
       UPDATE `catalog_product_entity_varchar` SET value='' WHERE attribute_id=103 OR attribute_id=105;
       清除所有产品URL
       UPDATE `catalog_product_entity_varchar` SET value='' WHERE attribute_id=481;
       清除所有分类描述:
       UPDATE `catalog_category_entity_text` SET value='' WHERE attribute_id=112 OR attribute_id=115 OR attribute_id=116;
       清除所有分类URL
       UPDATE `catalog_category_entity_varchar` SET value='' WHERE attribute_id=479;

11. Magento 数据库清理
       安全模式:清理日常数据库的无用记录
            TRUNCATE TABLE  `log_visitor`;
            TRUNCATE TABLE  `log_visitor_info`;
            TRUNCATE TABLE  `log_url`;
            TRUNCATE TABLE  `log_url_info`;
        干净模式:清理数据库的无用记录
            TRUNCATE  `log_visitor` ;
            TRUNCATE  `log_url_info` ;
            TRUNCATE  `log_visitor_info` ;
            TRUNCATE  `dataflow_batch_import` ;
            TRUNCATE  `log_url` ;
            TRUNCATE  `report_event` ;
            TRUNCATE  `report_viewed_product_index` ;
            TRUNCATE  `log_visitor_online` ;
      备注:如果是转移网站,URL 重写表 core_url_rewrite 也可清空,转完站重建 URL 即可。

12. 批量修改 SKU、Meta、Name 等字段里的部份词
        UPDATE `catalog_product_entity` SET sku=replace(sku,'oldskuw','newskuw') WHERE sku LIKE '%oldskuw%';
        UPDATE `catalog_product_entity_text` SET value=replace(value,'oldmetaw','newmetaw') WHERE value LIKE '%oldmetaw%';
        UPDATE `catalog_product_entity_varchar` SET value=replace(value,'oldnamew','newnamew') WHERE value LIKE '%oldnamew%';

13. 清站相关提示
      能在后台清理的就在后台清理,直接对数据库操作有造成网站出错的可能性。其他辅助命令如下:
      清除订单命令:
      TRUNCATE `sales_flat_invoice`;
      TRUNCATE `sales_flat_invoice_grid`;
      TRUNCATE `sales_flat_invoice_item`;
      TRUNCATE `sales_flat_order`;
      TRUNCATE `sales_flat_order_address`;
      TRUNCATE `sales_flat_order_grid`;
      TRUNCATE `sales_flat_order_item`;
      TRUNCATE `sales_flat_order_payment`;
      TRUNCATE `sales_flat_order_status_history`;
      TRUNCATE `sales_flat_quote`;
      TRUNCATE `sales_flat_quote_address`;
      TRUNCATE `sales_flat_quote_address_item`;
      TRUNCATE `sales_flat_quote_item`;
      TRUNCATE `sales_flat_quote_item_option`;
      TRUNCATE `sales_flat_quote_payment`;
      TRUNCATE `sales_flat_quote_shipping_rate`;
       清除其它日志:
      TRUNCATE `log_url_info`;
      TRUNCATE `log_visitor_info`;
      TRUNCATE `log_url`;
      TRUNCATE `log_visitor`;
      TRUNCATE `core_url_rewrite`;
      TRUNCATE `report_event`;
      TRUNCATE `report_viewed_product_index`;

14. 重置 Magento 所有 ID 统计数(如订单编码、发票编码等)
      TRUNCATE `eav_entity_store`;
      ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

15. 批量调整指定产品的价格
      create table xytmptb SELECT entity_id,value FROM `catalog_product_entity_varchar` WHERE (value LIKE '%Boot%' OR value LIKE '%Shoes%') AND attribute_id=60;
      UPDATE `catalog_product_entity_decimal` SET value=value+10 WHERE entity_id IN (SELECT entity_id FROM `xytmptb`) AND attribute_id=64;
      drop table xytmptb;
      最后别忘了重建价格索引!

16. 分类与产品的反向开关
      UPDATE `catalog_category_entity_int` SET value=if(value=0,1,0) WHERE attribute_id=119;
      UPDATE `catalog_product_entity_int` SET  value=if(value=1,2,1) WHERE attribute_id=273;
      运行一下,开的关了,关的开了,再运一下反之,最后别忘了重建索引!

17. 批量禁用产品 —— 数据库操作
      CREATE TABLE XYTMPTB SELECT entity_id,value FROM  `catalog_product_entity_varchar` WHERE  value LIKE 'affliction%' AND attribute_id=96;
      UPDATE `catalog_product_entity_int` SET value=1 WHERE attribute_id=273 AND entity_id IN (SELECT entity_id FROM `XYTMPTB`);
      DROP TABLE XYTMPTB;
      别忘了重建索引!

18. 账户之间转移订单
      UPDATE `sales_order` SET `customer_id`=newuserid WHERE `customer_id`=olduserid ;