31 lines
1.3 KiB
MySQL
31 lines
1.3 KiB
MySQL
|
-- 资源虚拟支付：同一用户、同一规格有效订单防重
|
|||
|
|
-- 前置条件：已经执行 sql/virtual_pay_resource_specs.sql。
|
|||
|
|
-- 执行前请先备份数据库。
|
|||
|
|
|
|||
|
|
-- 下面的查询必须返回空结果。
|
|||
|
|
-- 如果存在结果，说明上线防重前已经产生重复待支付/已支付订单，
|
|||
|
|
-- 需要先结合微信订单状态人工核对，不能直接批量关闭或删除。
|
|||
|
|
SELECT user_id,
|
|||
|
|
resource_id,
|
|||
|
|
COALESCE(resource_list_id, 0) AS resource_list_key,
|
|||
|
|
COUNT(*) AS active_order_count,
|
|||
|
|
GROUP_CONCAT(order_no ORDER BY id) AS order_nos
|
|||
|
|
FROM app_virtual_order
|
|||
|
|
WHERE status IN (0, 1)
|
|||
|
|
GROUP BY user_id, resource_id, COALESCE(resource_list_id, 0)
|
|||
|
|
HAVING COUNT(*) > 1;
|
|||
|
|
|
|||
|
|
-- status=0（待支付）和 status=1（已支付）共用一个唯一购买键。
|
|||
|
|
-- 退款或关闭后键值自动变为 NULL，允许用户重新购买。
|
|||
|
|
ALTER TABLE app_virtual_order
|
|||
|
|
ADD COLUMN active_purchase_key VARCHAR(80)
|
|||
|
|
GENERATED ALWAYS AS (
|
|||
|
|
CASE
|
|||
|
|
WHEN status IN (0, 1)
|
|||
|
|
THEN CONCAT(user_id, ':', resource_id, ':', COALESCE(resource_list_id, 0))
|
|||
|
|
ELSE NULL
|
|||
|
|
END
|
|||
|
|
) STORED COMMENT '待支付/已支付订单防重键' AFTER last_query_time,
|
|||
|
|
ADD UNIQUE KEY uk_virtual_order_active_purchase (active_purchase_key);
|
|||
|
|
|