| 1234567891011121314151617181920212223242526272829303132 |
- -- ============================================
- -- migration-v5.sql
- -- 邀请文案微信昵称 & 支付渠道切换追溯相关字段
- -- ============================================
- -- 1. t_user 补充微信昵称字段（幂等 ALTER）
- SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't_user' AND COLUMN_NAME = 'wechat_nickname');
- SET @sql = IF(@col_exists = 0, "ALTER TABLE `t_user` ADD COLUMN `wechat_nickname` VARCHAR(128) DEFAULT NULL COMMENT '微信昵称' AFTER `wechat_union_id`", 'SELECT 1');
- PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
- -- 2. t_payment_order 补充实际支付渠道字段（幂等 ALTER）
- SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't_payment_order' AND COLUMN_NAME = 'paid_channel');
- SET @sql = IF(@col_exists = 0, "ALTER TABLE `t_payment_order` ADD COLUMN `paid_channel` VARCHAR(20) DEFAULT NULL COMMENT '实际支付渠道：第一次支付成功时的渠道' AFTER `qr_code_url`", 'SELECT 1');
- PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
- -- 3. 支付订单重复支付记录表（切换渠道后多个渠道都支付成功时保留证据）
- CREATE TABLE IF NOT EXISTS `t_payment_order_extra_payment` (
- `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
- `order_no` VARCHAR(32) NOT NULL COMMENT '订单号',
- `channel` VARCHAR(20) NOT NULL COMMENT '实际支付渠道：WECHAT/ALIPAY',
- `trade_no` VARCHAR(64) NOT NULL COMMENT '第三方交易号',
- `amount` DECIMAL(10,2) NOT NULL COMMENT '支付金额（元）',
- `paid_time` DATETIME NOT NULL COMMENT '支付时间',
- `refund_status` VARCHAR(20) DEFAULT 'PENDING' COMMENT '退款状态：PENDING/SUCCESS/FAILED',
- `refund_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '退款交易号（第三方退款单号）',
- `refund_time` DATETIME DEFAULT NULL COMMENT '退款时间',
- `refund_fail_reason` VARCHAR(500) DEFAULT NULL COMMENT '退款失败原因',
- `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
- `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
- PRIMARY KEY (`id`),
- KEY `idx_order_no` (`order_no`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付订单重复支付记录表';
|
