a8cdc763cb
chore(brand): 统一将品牌名从雄戈小酒更改为橙虎小酒 - 更新alisms插件配置中的签名值 - 修改shopro插件中所有SQL脚本的品牌名称引用 - 更新商城配置、商品分类、演示数据中的品牌名称 - 替换后台登录页面的品牌标识 - 更新汇付分账配置中的媒介名称 - 修改登录预览生成脚本中的品牌显示 ```
110 行
3.9 KiB
SQL
110 行
3.9 KiB
SQL
-- 橙虎小酒：清理旧「小众厨房设备」遗留的非酒类商品
|
||
-- 策略：软删除（deletetime + 下架），保留酒类/雄戈演示商品
|
||
-- 执行前：请先备份 fa_shopro_goods 及相关表
|
||
-- 执行后：后台清除 Shopro 缓存 → 商品管理刷新列表
|
||
|
||
SET @now = UNIX_TIMESTAMP();
|
||
|
||
-- ============================================================
|
||
-- 第一步：预览将被清理的商品（务必先执行，确认无误后再执行下方 UPDATE）
|
||
-- ============================================================
|
||
-- SELECT id, title, price, stock, status, category_ids
|
||
-- FROM `fa_shopro_goods`
|
||
-- WHERE `deletetime` IS NULL
|
||
-- AND NOT (
|
||
-- `title` LIKE '%酒%'
|
||
-- OR `title` LIKE '%雄戈%'
|
||
-- OR `title` LIKE '【演示】%'
|
||
-- OR `title` LIKE '%白酒%'
|
||
-- OR `title` LIKE '%酱香%'
|
||
-- OR `title` LIKE '%浓香%'
|
||
-- OR `title` LIKE '%品鉴%'
|
||
-- OR `title` LIKE '%商务宴请%'
|
||
-- OR `title` LIKE '%单瓶装%'
|
||
-- OR `title` LIKE '%礼盒装%'
|
||
-- )
|
||
-- ORDER BY id;
|
||
|
||
-- 截图中的典型无关商品（单独核对）：
|
||
-- SELECT id, title FROM `fa_shopro_goods`
|
||
-- WHERE `deletetime` IS NULL
|
||
-- AND (
|
||
-- `title` LIKE '%塑料凳%'
|
||
-- OR `title` LIKE '%漏勺%'
|
||
-- OR `title` LIKE '%纸碗%'
|
||
-- OR `title` LIKE '%富强%'
|
||
-- OR `title` LIKE '%家益净%'
|
||
-- OR `title` LIKE '%叶美%'
|
||
-- );
|
||
|
||
-- ============================================================
|
||
-- 第二步：收集待清理商品 ID（临时表，便于关联清理）
|
||
-- ============================================================
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_remove_goods`;
|
||
CREATE TEMPORARY TABLE `_tmp_remove_goods` AS
|
||
SELECT `id`
|
||
FROM `fa_shopro_goods`
|
||
WHERE `deletetime` IS NULL
|
||
AND NOT (
|
||
`title` LIKE '%酒%'
|
||
OR `title` LIKE '%雄戈%'
|
||
OR `title` LIKE '【演示】%'
|
||
OR `title` LIKE '%白酒%'
|
||
OR `title` LIKE '%酱香%'
|
||
OR `title` LIKE '%浓香%'
|
||
OR `title` LIKE '%品鉴%'
|
||
OR `title` LIKE '%商务宴请%'
|
||
OR `title` LIKE '%单瓶装%'
|
||
OR `title` LIKE '%礼盒装%'
|
||
);
|
||
|
||
-- 预览数量：
|
||
-- SELECT COUNT(*) AS remove_count FROM `_tmp_remove_goods`;
|
||
-- SELECT g.id, g.title FROM `fa_shopro_goods` g INNER JOIN `_tmp_remove_goods` t ON g.id = t.id ORDER BY g.id;
|
||
|
||
-- ============================================================
|
||
-- 第三步：软删除商品 + 关联数据
|
||
-- ============================================================
|
||
|
||
-- 商品主表：软删除并下架
|
||
UPDATE `fa_shopro_goods` g
|
||
INNER JOIN `_tmp_remove_goods` t ON g.`id` = t.`id`
|
||
SET g.`deletetime` = @now,
|
||
g.`status` = 'down',
|
||
g.`updatetime` = @now;
|
||
|
||
-- SKU 价格库存（有软删除）
|
||
UPDATE `fa_shopro_goods_sku_price` p
|
||
INNER JOIN `_tmp_remove_goods` t ON p.`goods_id` = t.`id`
|
||
SET p.`deletetime` = @now,
|
||
p.`updatetime` = @now
|
||
WHERE p.`deletetime` IS NULL;
|
||
|
||
-- SKU 规格项（无软删除，直接删）
|
||
DELETE p FROM `fa_shopro_goods_sku` p
|
||
INNER JOIN `_tmp_remove_goods` t ON p.`goods_id` = t.`id`;
|
||
|
||
-- 购物车
|
||
UPDATE `fa_shopro_cart` c
|
||
INNER JOIN `_tmp_remove_goods` t ON c.`goods_id` = t.`id`
|
||
SET c.`deletetime` = @now,
|
||
c.`updatetime` = @now
|
||
WHERE c.`deletetime` IS NULL;
|
||
|
||
-- 用户收藏
|
||
UPDATE `fa_shopro_user_favorite` f
|
||
INNER JOIN `_tmp_remove_goods` t ON f.`goods_id` = t.`id`
|
||
SET f.`deletetime` = @now,
|
||
f.`updatetime` = @now
|
||
WHERE f.`deletetime` IS NULL;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS `_tmp_remove_goods`;
|
||
|
||
-- ============================================================
|
||
-- 第四步：验证
|
||
-- ============================================================
|
||
-- 应只剩酒类商品：
|
||
-- SELECT id, title, status, deletetime FROM `fa_shopro_goods` WHERE `deletetime` IS NULL ORDER BY id;
|
||
-- 已删除的旧商品：
|
||
-- SELECT id, title, FROM_UNIXTIME(deletetime) AS deleted_at FROM `fa_shopro_goods` WHERE `deletetime` = @now ORDER BY id;
|