78 lines
3.0 KiB
SQL
78 lines
3.0 KiB
SQL
-- +goose Up
|
|
|
|
-- 退款补偿重试字段：限制失败补偿的自动重试次数，并支持退避。
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND COLUMN_NAME = 'retry_count') = 0,
|
|
'ALTER TABLE payment_orders ADD COLUMN retry_count INT NOT NULL DEFAULT 0 COMMENT ''自动补偿重试次数'' AFTER raw_response',
|
|
'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 = 'last_retry_at') = 0,
|
|
'ALTER TABLE payment_orders ADD COLUMN last_retry_at DATETIME NULL COMMENT ''最近自动补偿重试时间'' AFTER retry_count',
|
|
'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 = 'next_retry_at') = 0,
|
|
'ALTER TABLE payment_orders ADD COLUMN next_retry_at DATETIME NULL COMMENT ''下次允许自动补偿重试时间'' AFTER last_retry_at',
|
|
'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_refund_retry') = 0,
|
|
'CREATE INDEX idx_payment_orders_refund_retry ON payment_orders (biz_type, status, next_retry_at, updated_at, id)',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- +goose Down
|
|
|
|
SET @sql = IF(
|
|
(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'payment_orders' AND INDEX_NAME = 'idx_payment_orders_refund_retry') > 0,
|
|
'ALTER TABLE payment_orders DROP INDEX idx_payment_orders_refund_retry',
|
|
'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 = 'next_retry_at') > 0,
|
|
'ALTER TABLE payment_orders DROP COLUMN next_retry_at',
|
|
'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 = 'last_retry_at') > 0,
|
|
'ALTER TABLE payment_orders DROP COLUMN last_retry_at',
|
|
'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 = 'retry_count') > 0,
|
|
'ALTER TABLE payment_orders DROP COLUMN retry_count',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|