OpenCart 3.0 分类与产品清理操作文档

2025年11月27日 0 主题 by

  1. 保留的分类列表

SELECT * FROM `oc_category_description` WHERE `name` LIKE ‘%FRANCE%’

结果：542

SELECT * FROM `oc_category` WHERE `parent_id` = 542

以下分类为需要保留的分类 ID（包含 542 及其子分类）：

69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542

    1. 已执行操作：删除所有非指定分类
      以下 SQL 已执行，用于删除所有非指定分类及其 SEO、布局、店铺绑定等关联：
      DELETE FROM oc_category_description
      WHERE category_id NOT IN (69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542);

    DELETE FROM oc_category_path
    WHERE category_id NOT IN (69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542);

    DELETE FROM oc_category_to_store
    WHERE category_id NOT IN (69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542);

    DELETE FROM oc_category_to_layout
    WHERE category_id NOT IN (69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542);

    DELETE FROM oc_seo_url
    WHERE query LIKE ‘category_id=%’
    AND REPLACE(query, ‘category_id=’, ”) NOT IN (’69’,’544′,’548′,’549′,’553′,’615′,’660′,’661′,’700′,’707′,’724′,’788′,’789′,’790′,’791′,’792′,’808′,’809′,’826′,’866′,’867′,’869′,’870′,’885′,’542′);

    DELETE FROM oc_category
    WHERE category_id NOT IN (69, 544, 548, 549, 553, 615, 660, 661, 700, 707, 724, 788, 789, 790, 791, 792, 808, 809, 826, 866, 867, 869, 870, 885, 542);

    1. 已执行操作：删除所有不属于指定分类的产品（方案 3：NOT IN 子查询）

    以下 SQL 已执行，用于删除所有不属于指定分类的产品及其所有关联记录。

    3.1 产品属性（attribute）

    DELETE FROM oc_product_attributeWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.2 产品描述（description）

    DELETE FROM oc_product_descriptionWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.3 产品折扣（discount）

    DELETE FROM oc_product_discountWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.4 产品图片（image）

    DELETE FROM oc_product_imageWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.5 产品选项（option / option value）

    DELETE FROM oc_product_optionWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) ); DELETE FROM oc_product_option_valueWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.6 关联产品（related）

    DELETE FROM oc_product_relatedWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) )OR related_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.7 奖励积分（reward）

    DELETE FROM oc_product_rewardWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.8 特别价格（special）

    DELETE FROM oc_product_specialWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.9 下载关联（product_to_download）

    DELETE FROM oc_product_to_downloadWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.10 布局关联（product_to_layout）

    DELETE FROM oc_product_to_layoutWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.11 Store 关联（product_to_store）

    DELETE FROM oc_product_to_storeWHERE product_id NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.12 SEO URL

    DELETE FROM oc_seo_url WHERE query LIKE 'product_id=%' AND REPLACE(query,'product_id=','') NOT IN (SELECT product_id FROM oc_product_to_categoryWHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.13 删除产品本体

    DELETE FROM oc_product WHERE product_id NOT IN (SELECT product_id FROM oc_product_to_category WHERE category_id IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542) );

    3.14 清理多余的 product_to_category 记录

    DELETE FROM oc_product_to_categoryWHERE category_id NOT IN (69,544,548,549,553,615,660,661,700,707,724,788,789,790,791,792,808,809,826,866,867,869,870,885,542);

    1. 执行结果

    执行完上述操作后：

    • 系统中仅保留分类 542 + 指定子分类
    • 所有其他分类已被删除
    • 所有不属于指定分类的产品及其关联记录已全部清理
    • 数据库结构保持完整，无残留无效关联

    Comments

    发表回复

    Your email address will not be published. Name and email are required