81 lines
3.0 KiB
SQL
81 lines
3.0 KiB
SQL
-- +goose Up
|
|
|
|
-- 支付单记录命中的支付配置 ID，后续查询、退款和通知验签优先回溯原配置。
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND COLUMN_NAME = 'payment_config_id') = 0,
|
|
'ALTER TABLE payment_orders ADD COLUMN payment_config_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ''命中的支付配置ID'' AFTER user_id',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 支付单按配置 ID 查询，用于回调、退款和后台排障。
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND INDEX_NAME = 'idx_payment_orders_payment_config_id') = 0,
|
|
'ALTER TABLE payment_orders ADD KEY idx_payment_orders_payment_config_id (payment_config_id)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 支付配置按支付方式筛选默认渠道，支持微信和支付宝分别启用。
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_merchant_configs' AND INDEX_NAME = 'idx_payment_merchant_configs_pay_way') = 0,
|
|
'ALTER TABLE payment_merchant_configs ADD KEY idx_payment_merchant_configs_pay_way (pay_way, status, is_default)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 历史支付单按服务商、商户号和支付方式回填命中的配置 ID。
|
|
UPDATE payment_orders p
|
|
SET payment_config_id = (
|
|
SELECT c.id
|
|
FROM payment_merchant_configs c
|
|
WHERE c.provider = p.provider
|
|
AND c.merchant_id = p.merchant_id
|
|
AND c.pay_way = p.pay_way
|
|
ORDER BY c.status = 'active' DESC, c.is_default DESC, c.id DESC
|
|
LIMIT 1
|
|
)
|
|
WHERE p.payment_config_id = 0
|
|
AND EXISTS (
|
|
SELECT 1
|
|
FROM payment_merchant_configs c
|
|
WHERE c.provider = p.provider
|
|
AND c.merchant_id = p.merchant_id
|
|
AND c.pay_way = p.pay_way
|
|
);
|
|
|
|
-- +goose Down
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_merchant_configs' AND INDEX_NAME = 'idx_payment_merchant_configs_pay_way') > 0,
|
|
'ALTER TABLE payment_merchant_configs DROP INDEX idx_payment_merchant_configs_pay_way',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND INDEX_NAME = 'idx_payment_orders_payment_config_id') > 0,
|
|
'ALTER TABLE payment_orders DROP INDEX idx_payment_orders_payment_config_id',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND COLUMN_NAME = 'payment_config_id') > 0,
|
|
'ALTER TABLE payment_orders DROP COLUMN payment_config_id',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|