25 lines
883 B
SQL
25 lines
883 B
SQL
-- 添加订单过期时间字段和复合索引
|
||
DO $$
|
||
BEGIN
|
||
-- 添加 expires_at 字段（订单过期时间，NULL 表示不过期）
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name='tb_order'
|
||
AND column_name='expires_at'
|
||
) THEN
|
||
ALTER TABLE tb_order ADD COLUMN expires_at TIMESTAMPTZ;
|
||
COMMENT ON COLUMN tb_order.expires_at IS '订单过期时间（NULL表示不过期，待支付订单默认30分钟后过期）';
|
||
END IF;
|
||
|
||
-- 创建复合索引：用于定时任务扫描超时待支付订单
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM pg_indexes
|
||
WHERE tablename='tb_order'
|
||
AND indexname='idx_order_expires'
|
||
) THEN
|
||
CREATE INDEX idx_order_expires ON tb_order (expires_at, payment_status)
|
||
WHERE expires_at IS NOT NULL AND deleted_at IS NULL;
|
||
END IF;
|
||
END
|
||
$$;
|