java-bbc/db/2024-06-25 秒杀订单改造.sql

USE yami_bbc;

更新后与原秒杀流程有冲突，更新前需要关闭当前的所有秒杀活动，并再根据redis.aof.databases 配置的库删除键：mall4j_seckill:seckill_order_lock_log


-- 订单添加使用积分字段
ALTER TABLE `tz_order`
    ADD COLUMN `score` bigint NOT NULL DEFAULT '0' COMMENT '使用积分' AFTER `shop_change_free_amount`;

-- 根据订单项中的使用积分数据，更新订单
UPDATE `tz_order` o
    JOIN (
    SELECT `order_number`, SUM(`use_score`) AS total_use_score
    FROM `tz_order_item`
    GROUP BY `order_number`
    ) oi ON o.`order_number` = oi.`order_number`
    SET o.`score` = oi.`total_use_score`;


ALTER TABLE `tz_seckill`
DROP COLUMN max_cancel_time;