215 lines
10 KiB
SQL
215 lines
10 KiB
SQL
-- 微信支付相关数据表
|
||
-- 创建时间：2025-01-27
|
||
-- 说明：包含微信支付订单、退款、配置、日志等表结构
|
||
|
||
SET NAMES utf8mb4;
|
||
SET CHARACTER SET utf8mb4;
|
||
|
||
-- 使用xiaozhi数据库
|
||
USE `xiaozhi`;
|
||
|
||
-- =============================================
|
||
-- 微信支付订单表
|
||
-- =============================================
|
||
DROP TABLE IF EXISTS `wechat_pay_order`;
|
||
CREATE TABLE `wechat_pay_order` (
|
||
`order_id` varchar(64) NOT NULL COMMENT '订单ID，主键',
|
||
`out_trade_no` varchar(32) NOT NULL COMMENT '商户订单号',
|
||
`transaction_id` varchar(32) DEFAULT NULL COMMENT '微信支付订单号',
|
||
`openid` varchar(128) NOT NULL COMMENT '用户openid',
|
||
`device_id` varchar(255) DEFAULT NULL COMMENT '设备ID',
|
||
`body` varchar(128) NOT NULL COMMENT '商品描述',
|
||
`detail` text COMMENT '商品详情',
|
||
`attach` varchar(127) DEFAULT NULL COMMENT '附加数据',
|
||
`total_fee` int NOT NULL COMMENT '订单金额（分）',
|
||
`amount` decimal(10,2) NOT NULL COMMENT '订单金额（元）',
|
||
`fee_type` varchar(16) DEFAULT 'CNY' COMMENT '货币类型',
|
||
`spbill_create_ip` varchar(64) DEFAULT NULL COMMENT '终端IP',
|
||
`trade_type` varchar(16) DEFAULT 'JSAPI' COMMENT '交易类型',
|
||
`goods_tag` varchar(32) DEFAULT NULL COMMENT '商品标记',
|
||
`notify_url` varchar(256) DEFAULT NULL COMMENT '通知地址',
|
||
`trade_state` varchar(32) DEFAULT 'NOTPAY' COMMENT '订单状态：NOTPAY-未支付，SUCCESS-支付成功，REFUND-转入退款，CLOSED-已关闭，REVOKED-已撤销，USERPAYING-用户支付中，PAYERROR-支付失败',
|
||
`trade_state_desc` varchar(256) DEFAULT NULL COMMENT '订单状态描述',
|
||
`time_end` datetime DEFAULT NULL COMMENT '支付完成时间',
|
||
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`time_expire` datetime DEFAULT NULL COMMENT '订单失效时间',
|
||
`prepay_id` varchar(64) DEFAULT NULL COMMENT '预支付交易会话标识',
|
||
`bank_type` varchar(32) DEFAULT NULL COMMENT '支付银行',
|
||
`cash_fee` int DEFAULT NULL COMMENT '现金支付金额',
|
||
`cash_fee_type` varchar(16) DEFAULT NULL COMMENT '现金支付货币类型',
|
||
`rate` varchar(16) DEFAULT NULL COMMENT '汇率',
|
||
`coupon_fee` int DEFAULT NULL COMMENT '代金券金额',
|
||
`coupon_count` int DEFAULT NULL COMMENT '代金券使用数量',
|
||
`mch_id` varchar(32) DEFAULT NULL COMMENT '微信支付分配的商户号',
|
||
`app_id` varchar(32) DEFAULT NULL COMMENT '微信分配的小程序ID',
|
||
`nonce_str` varchar(32) DEFAULT NULL COMMENT '随机字符串',
|
||
`sign` varchar(64) DEFAULT NULL COMMENT '签名',
|
||
`sign_type` varchar(32) DEFAULT 'MD5' COMMENT '签名类型',
|
||
`deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`order_id`),
|
||
UNIQUE KEY `uk_out_trade_no` (`out_trade_no`),
|
||
KEY `idx_transaction_id` (`transaction_id`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_device_id` (`device_id`),
|
||
KEY `idx_trade_state` (`trade_state`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_deleted` (`deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付订单表';
|
||
|
||
-- =============================================
|
||
-- 微信支付退款表
|
||
-- =============================================
|
||
DROP TABLE IF EXISTS `wechat_pay_refund`;
|
||
CREATE TABLE `wechat_pay_refund` (
|
||
`refund_id` varchar(64) NOT NULL COMMENT '退款ID，主键',
|
||
`out_trade_no` varchar(32) NOT NULL COMMENT '商户订单号',
|
||
`transaction_id` varchar(32) DEFAULT NULL COMMENT '微信支付订单号',
|
||
`out_refund_no` varchar(64) NOT NULL COMMENT '商户退款单号',
|
||
`refund_id_wx` varchar(32) DEFAULT NULL COMMENT '微信退款单号',
|
||
`refund_channel` varchar(16) DEFAULT NULL COMMENT '退款渠道',
|
||
`refund_fee` int NOT NULL COMMENT '退款金额（分）',
|
||
`refund_amount` decimal(10,2) NOT NULL COMMENT '退款金额（元）',
|
||
`total_fee` int NOT NULL COMMENT '订单总金额（分）',
|
||
`total_amount` decimal(10,2) NOT NULL COMMENT '订单总金额（元）',
|
||
`fee_type` varchar(16) DEFAULT 'CNY' COMMENT '货币类型',
|
||
`cash_fee` int DEFAULT NULL COMMENT '现金支付金额',
|
||
`cash_refund_fee` int DEFAULT NULL COMMENT '现金退款金额',
|
||
`coupon_refund_fee` int DEFAULT NULL COMMENT '代金券退款金额',
|
||
`coupon_refund_count` int DEFAULT NULL COMMENT '退款代金券使用数量',
|
||
`refund_status` varchar(32) DEFAULT 'PROCESSING' COMMENT '退款状态：SUCCESS-退款成功，REFUNDCLOSE-退款关闭，PROCESSING-退款处理中，CHANGE-退款异常',
|
||
`refund_account` varchar(80) DEFAULT NULL COMMENT '退款资金来源',
|
||
`refund_recv_accout` varchar(64) DEFAULT NULL COMMENT '退款入账账户',
|
||
`refund_success_time` datetime DEFAULT NULL COMMENT '退款成功时间',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`refund_id`),
|
||
UNIQUE KEY `uk_out_refund_no` (`out_refund_no`),
|
||
KEY `idx_out_trade_no` (`out_trade_no`),
|
||
KEY `idx_transaction_id` (`transaction_id`),
|
||
KEY `idx_refund_id_wx` (`refund_id_wx`),
|
||
KEY `idx_refund_status` (`refund_status`),
|
||
KEY `idx_create_time` (`create_time`),
|
||
KEY `idx_deleted` (`deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付退款表';
|
||
|
||
-- =============================================
|
||
-- 微信支付配置表
|
||
-- =============================================
|
||
DROP TABLE IF EXISTS `wechat_pay_config`;
|
||
CREATE TABLE `wechat_pay_config` (
|
||
`config_id` int NOT NULL AUTO_INCREMENT COMMENT '配置ID，主键',
|
||
`config_name` varchar(100) NOT NULL COMMENT '配置名称',
|
||
`app_id` varchar(32) NOT NULL COMMENT '应用ID',
|
||
`mch_id` varchar(32) NOT NULL COMMENT '商户号',
|
||
`partner_key` varchar(32) NOT NULL COMMENT '商户密钥',
|
||
`sign_type` varchar(32) DEFAULT 'MD5' COMMENT '签名类型',
|
||
`cert_path` varchar(500) DEFAULT NULL COMMENT '证书路径',
|
||
`cert_password` varchar(100) DEFAULT NULL COMMENT '证书密码',
|
||
`notify_url` varchar(500) DEFAULT NULL COMMENT '支付回调通知URL',
|
||
`refund_notify_url` varchar(500) DEFAULT NULL COMMENT '退款回调通知URL',
|
||
`sandbox` tinyint DEFAULT 0 COMMENT '是否沙箱环境：0-正式环境，1-沙箱环境',
|
||
`status` tinyint DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`config_id`),
|
||
UNIQUE KEY `uk_config_name` (`config_name`),
|
||
KEY `idx_app_id` (`app_id`),
|
||
KEY `idx_mch_id` (`mch_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付配置表';
|
||
|
||
-- =============================================
|
||
-- 微信支付日志表
|
||
-- =============================================
|
||
DROP TABLE IF EXISTS `wechat_pay_log`;
|
||
CREATE TABLE `wechat_pay_log` (
|
||
`log_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID，主键',
|
||
`trade_no` varchar(64) DEFAULT NULL COMMENT '交易单号（订单号或退款单号）',
|
||
`log_type` varchar(32) NOT NULL COMMENT '日志类型：ORDER-订单，REFUND-退款，NOTIFY-回调通知，QUERY-查询',
|
||
`operation` varchar(64) NOT NULL COMMENT '操作类型：CREATE-创建，UPDATE-更新，QUERY-查询，NOTIFY-通知',
|
||
`request_data` text COMMENT '请求数据',
|
||
`response_data` text COMMENT '响应数据',
|
||
`result_code` varchar(16) DEFAULT NULL COMMENT '结果代码',
|
||
`result_msg` varchar(500) DEFAULT NULL COMMENT '结果消息',
|
||
`error_code` varchar(32) DEFAULT NULL COMMENT '错误代码',
|
||
`error_msg` varchar(500) DEFAULT NULL COMMENT '错误消息',
|
||
`cost_time` int DEFAULT NULL COMMENT '耗时（毫秒）',
|
||
`ip_address` varchar(64) DEFAULT NULL COMMENT 'IP地址',
|
||
`user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`log_id`),
|
||
KEY `idx_trade_no` (`trade_no`),
|
||
KEY `idx_log_type` (`log_type`),
|
||
KEY `idx_operation` (`operation`),
|
||
KEY `idx_result_code` (`result_code`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信支付日志表';
|
||
|
||
-- =============================================
|
||
-- 插入默认配置数据
|
||
-- =============================================
|
||
INSERT INTO `wechat_pay_config` (
|
||
`config_name`,
|
||
`app_id`,
|
||
`mch_id`,
|
||
`partner_key`,
|
||
`sign_type`,
|
||
`notify_url`,
|
||
`refund_notify_url`,
|
||
`sandbox`,
|
||
`status`,
|
||
`remark`
|
||
) VALUES (
|
||
'默认微信支付配置',
|
||
'wxff56c34ef9aceb62',
|
||
'1234567890',
|
||
'your_partner_key_here',
|
||
'MD5',
|
||
'http://localhost:8091/api/wechat/pay/notify',
|
||
'http://localhost:8091/api/wechat/pay/refund/notify',
|
||
0,
|
||
1,
|
||
'系统默认微信支付配置，请根据实际情况修改'
|
||
);
|
||
|
||
-- =============================================
|
||
-- 创建视图：订单统计视图
|
||
-- =============================================
|
||
DROP VIEW IF EXISTS `v_wechat_pay_order_stats`;
|
||
CREATE VIEW `v_wechat_pay_order_stats` AS
|
||
SELECT
|
||
DATE(create_time) as order_date,
|
||
trade_state,
|
||
COUNT(*) as order_count,
|
||
SUM(total_fee) as total_fee_sum,
|
||
SUM(amount) as total_amount_sum,
|
||
AVG(total_fee) as avg_fee,
|
||
AVG(amount) as avg_amount
|
||
FROM `wechat_pay_order`
|
||
WHERE deleted = 0
|
||
GROUP BY DATE(create_time), trade_state
|
||
ORDER BY order_date DESC, trade_state;
|
||
|
||
-- =============================================
|
||
-- 创建视图：退款统计视图
|
||
-- =============================================
|
||
DROP VIEW IF EXISTS `v_wechat_pay_refund_stats`;
|
||
CREATE VIEW `v_wechat_pay_refund_stats` AS
|
||
SELECT
|
||
DATE(create_time) as refund_date,
|
||
refund_status,
|
||
COUNT(*) as refund_count,
|
||
SUM(refund_fee) as total_refund_fee,
|
||
SUM(refund_amount) as total_refund_amount,
|
||
AVG(refund_fee) as avg_refund_fee,
|
||
AVG(refund_amount) as avg_refund_amount
|
||
FROM `wechat_pay_refund`
|
||
WHERE deleted = 0
|
||
GROUP BY DATE(create_time), refund_status
|
||
ORDER BY refund_date DESC, refund_status; |