27 lines
1.4 KiB
SQL
27 lines
1.4 KiB
SQL
-- 创建支付订单表
|
|
CREATE TABLE IF NOT EXISTS `payment_orders` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
|
|
`user_id` int(11) NOT NULL COMMENT '用户ID',
|
|
`out_trade_no` varchar(64) NOT NULL COMMENT '商户订单号',
|
|
`transaction_id` varchar(64) DEFAULT NULL COMMENT '微信支付订单号',
|
|
`total_fee` int(11) NOT NULL COMMENT '订单金额（分）',
|
|
`body` varchar(128) NOT NULL COMMENT '商品描述',
|
|
`trade_type` varchar(16) NOT NULL COMMENT '交易类型',
|
|
`prepay_id` varchar(64) DEFAULT NULL COMMENT '预支付交易会话标识',
|
|
`mweb_url` text DEFAULT NULL COMMENT 'H5支付跳转链接',
|
|
`status` enum('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending' COMMENT '支付状态',
|
|
`paid_at` datetime DEFAULT NULL COMMENT '支付完成时间',
|
|
`created_at` datetime NOT NULL COMMENT '创建时间',
|
|
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
|
|
KEY `idx_user_id` (`user_id`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单表';
|
|
|
|
-- 为用户表添加支付状态字段
|
|
ALTER TABLE `users` ADD COLUMN `payment_status` enum('unpaid','paid') NOT NULL DEFAULT 'unpaid' COMMENT '支付状态' AFTER `status`;
|
|
|
|
-- 添加索引
|
|
ALTER TABLE `users` ADD KEY `idx_payment_status` (`payment_status`); |