90 lines
2.8 KiB
SQL
90 lines
2.8 KiB
SQL
-- =========================
|
||
-- 1. 表结构变更：新增 payment_merchant
|
||
-- =========================
|
||
|
||
-- 1.1 order 表：增加支付商户标识
|
||
ALTER TABLE `order`
|
||
ADD COLUMN `payment_merchant` varchar(64) NOT NULL DEFAULT '' COMMENT '支付商户标识，例如 one/two' AFTER `payment_scene`;
|
||
|
||
-- 1.2 order_refund 表：增加支付商户标识
|
||
ALTER TABLE `order_refund`
|
||
ADD COLUMN `payment_merchant` varchar(64) NOT NULL DEFAULT '' COMMENT '退款对应的支付商户标识，例如 one/two' AFTER `product_id`;
|
||
|
||
-- 1.3 agent_membership_recharge_order 表：增加支付商户标识
|
||
ALTER TABLE `agent_membership_recharge_order`
|
||
ADD COLUMN `payment_merchant` varchar(64) NOT NULL DEFAULT '' COMMENT '支付商户标识，例如 one/two' AFTER `payment_method`;
|
||
|
||
-- =========================
|
||
-- 2. 历史数据初始化：one / two
|
||
-- 约定：
|
||
-- - one：当前主支付宝商户
|
||
-- - two：当前 bak 支付宝商户
|
||
-- 时间区间：
|
||
-- [2026-01-25 16:38:17, 2026-02-02 18:26:00)
|
||
-- =========================
|
||
|
||
-- 2.1 order 表：按时间区间映射 one / two
|
||
-- 仅处理支付宝订单（payment_platform='alipay'）
|
||
-- 区间内用 two，其余用 one
|
||
-- 时间优先用 pay_time，pay_time 为空则用 create_time
|
||
|
||
-- 2.1.1 全部支付宝订单默认标记为 one
|
||
UPDATE `order`
|
||
SET
|
||
payment_merchant = 'one'
|
||
WHERE
|
||
payment_platform = 'alipay'
|
||
AND del_state = 0;
|
||
|
||
-- 2.1.2 区间内的支付宝订单标记为 two
|
||
UPDATE `order`
|
||
SET
|
||
payment_merchant = 'two'
|
||
WHERE
|
||
payment_platform = 'alipay'
|
||
AND del_state = 0
|
||
AND (
|
||
(
|
||
pay_time IS NOT NULL
|
||
AND pay_time >= '2026-01-25 16:38:17'
|
||
AND pay_time < '2026-02-02 18:26:00'
|
||
)
|
||
OR (
|
||
pay_time IS NULL
|
||
AND create_time >= '2026-01-25 16:38:17'
|
||
AND create_time < '2026-02-02 18:26:00'
|
||
)
|
||
);
|
||
|
||
-- 2.2 agent_membership_recharge_order 表：按创建时间映射 one / two
|
||
-- 仅处理支付宝支付（payment_method='alipay'）
|
||
-- 区间内创建的订单标记为 two，其余为 one
|
||
|
||
-- 2.2.1 所有支付宝代理会员充值订单默认标记为 one
|
||
UPDATE `agent_membership_recharge_order`
|
||
SET
|
||
payment_merchant = 'one'
|
||
WHERE
|
||
payment_method = 'alipay'
|
||
AND del_state = 0;
|
||
|
||
-- 2.2.2 区间内的支付宝代理会员订单标记为 two
|
||
UPDATE `agent_membership_recharge_order`
|
||
SET
|
||
payment_merchant = 'two'
|
||
WHERE
|
||
payment_method = 'alipay'
|
||
AND del_state = 0
|
||
AND create_time >= '2026-01-25 16:38:17'
|
||
AND create_time < '2026-02-02 18:26:00';
|
||
|
||
-- 2.3 order_refund 表：跟随对应订单的 payment_merchant
|
||
-- 直接复制 order.payment_merchant，避免逻辑重复
|
||
|
||
UPDATE `order_refund` r
|
||
JOIN `order` o ON r.order_id = o.id
|
||
SET
|
||
r.payment_merchant = o.payment_merchant
|
||
WHERE
|
||
r.del_state = 0
|
||
AND o.del_state = 0; |