478 lines
17 KiB
Markdown
478 lines
17 KiB
Markdown
# 📋 核心支付流程 - 数据库设计
|
||
|
||
**设计日期**: 2026-01-23
|
||
**数据库**: peixue (MySQL)
|
||
|
||
---
|
||
|
||
## 📊 表结构设计
|
||
|
||
### 1. 订单表扩展 (order)
|
||
|
||
需要添加以下字段以支持多种支付方式：
|
||
|
||
```sql
|
||
-- 支付方式相关字段
|
||
ALTER TABLE `order` ADD COLUMN `payment_method` VARCHAR(50) COMMENT '支付方式：wallet,timecard,package,wechat,mixed' AFTER `payment_status`;
|
||
ALTER TABLE `order` ADD COLUMN `wallet_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '钱包支付金额';
|
||
ALTER TABLE `order` ADD COLUMN `timecard_id` BIGINT COMMENT '使用的次卡ID';
|
||
ALTER TABLE `order` ADD COLUMN `package_id` BIGINT COMMENT '使用的套餐ID';
|
||
ALTER TABLE `order` ADD COLUMN `coupon_id` BIGINT COMMENT '使用的优惠券ID';
|
||
ALTER TABLE `order` ADD COLUMN `coupon_discount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '优惠券折扣金额';
|
||
ALTER TABLE `order` ADD COLUMN `service_address_id` BIGINT COMMENT '服务地址ID';
|
||
|
||
-- 金额相关字段
|
||
ALTER TABLE `order` ADD COLUMN `original_amount` DECIMAL(10,2) COMMENT '原价' AFTER `actual_amount`;
|
||
ALTER TABLE `order` ADD COLUMN `discount_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '总折扣金额';
|
||
|
||
-- 结算相关字段
|
||
ALTER TABLE `order` ADD COLUMN `teacher_income` DECIMAL(10,2) DEFAULT 0.00 COMMENT '陪伴员收益';
|
||
ALTER TABLE `order` ADD COLUMN `manager_commission` DECIMAL(10,2) DEFAULT 0.00 COMMENT '管理师提成';
|
||
ALTER TABLE `order` ADD COLUMN `platform_income` DECIMAL(10,2) DEFAULT 0.00 COMMENT '平台收益';
|
||
ALTER TABLE `order` ADD COLUMN `settlement_status` TINYINT DEFAULT 0 COMMENT '结算状态：0-未结算，1-已结算';
|
||
ALTER TABLE `order` ADD COLUMN `settlement_time` DATETIME COMMENT '结算时间';
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `order` ADD INDEX `idx_timecard_id` (`timecard_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_package_id` (`package_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_coupon_id` (`coupon_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_service_address_id` (`service_address_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_settlement_status` (`settlement_status`);
|
||
```
|
||
|
||
---
|
||
|
||
### 2. 钱包表 (wallet)
|
||
|
||
检查是否存在，如不存在则创建：
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `wallet` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额',
|
||
`frozen_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '冻结金额',
|
||
`total_recharge` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计充值',
|
||
`total_consume` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计消费',
|
||
`total_withdraw` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '累计提现',
|
||
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_id` (`user_id`),
|
||
KEY `idx_tenant_id` (`tenant_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包表';
|
||
```
|
||
|
||
---
|
||
|
||
### 3. 钱包交易记录表 (wallet_transaction)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `wallet_transaction` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`wallet_id` BIGINT NOT NULL COMMENT '钱包ID',
|
||
`order_id` BIGINT COMMENT '关联订单ID',
|
||
`transaction_type` VARCHAR(50) NOT NULL COMMENT '交易类型：recharge-充值，consume-消费，refund-退款，withdraw-提现，income-收益',
|
||
`amount` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
|
||
`balance_before` DECIMAL(10,2) NOT NULL COMMENT '交易前余额',
|
||
`balance_after` DECIMAL(10,2) NOT NULL COMMENT '交易后余额',
|
||
`transaction_no` VARCHAR(100) COMMENT '交易流水号',
|
||
`payment_channel` VARCHAR(50) COMMENT '支付渠道：wechat,alipay',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-失败，1-成功，2-处理中',
|
||
`remark` VARCHAR(500) COMMENT '备注',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_wallet_id` (`wallet_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_transaction_no` (`transaction_no`),
|
||
KEY `idx_created_time` (`created_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包交易记录表';
|
||
```
|
||
|
||
---
|
||
|
||
### 4. 次卡表扩展 (time_card)
|
||
|
||
检查并添加必要字段：
|
||
|
||
```sql
|
||
-- 检查表是否存在
|
||
-- 如果存在，添加缺失字段
|
||
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `total_times` INT NOT NULL DEFAULT 0 COMMENT '总次数';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `used_times` INT NOT NULL DEFAULT 0 COMMENT '已使用次数';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `remaining_times` INT NOT NULL DEFAULT 0 COMMENT '剩余次数';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `expire_date` DATE COMMENT '过期日期';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `status` TINYINT DEFAULT 1 COMMENT '状态：0-已过期，1-使用中，2-已用完';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号';
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `time_card` ADD INDEX IF NOT EXISTS `idx_user_id` (`user_id`);
|
||
ALTER TABLE `time_card` ADD INDEX IF NOT EXISTS `idx_status` (`status`);
|
||
ALTER TABLE `time_card` ADD INDEX IF NOT EXISTS `idx_expire_date` (`expire_date`);
|
||
```
|
||
|
||
---
|
||
|
||
### 5. 次卡使用记录表 (time_card_usage)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `time_card_usage` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`timecard_id` BIGINT NOT NULL COMMENT '次卡ID',
|
||
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
||
`used_times` INT NOT NULL DEFAULT 1 COMMENT '使用次数',
|
||
`remaining_times` INT NOT NULL COMMENT '剩余次数',
|
||
`remark` VARCHAR(500) COMMENT '备注',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_timecard_id` (`timecard_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_created_time` (`created_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='次卡使用记录表';
|
||
```
|
||
|
||
---
|
||
|
||
### 6. 套餐表扩展 (package)
|
||
|
||
检查并添加必要字段：
|
||
|
||
```sql
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `total_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '总课时';
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `used_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '已使用课时';
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `remaining_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '剩余课时';
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `expire_date` DATE COMMENT '过期日期';
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `status` TINYINT DEFAULT 1 COMMENT '状态：0-已过期，1-使用中，2-已用完';
|
||
ALTER TABLE `package` ADD COLUMN IF NOT EXISTS `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号';
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `package` ADD INDEX IF NOT EXISTS `idx_user_id` (`user_id`);
|
||
ALTER TABLE `package` ADD INDEX IF NOT EXISTS `idx_status` (`status`);
|
||
ALTER TABLE `package` ADD INDEX IF NOT EXISTS `idx_expire_date` (`expire_date`);
|
||
```
|
||
|
||
---
|
||
|
||
### 7. 套餐使用记录表 (package_usage)
|
||
|
||
已存在，检查字段是否完整：
|
||
|
||
```sql
|
||
-- 检查 package_usage 表结构
|
||
-- 确保包含以下字段：
|
||
-- id, user_id, package_id, order_id, used_hours, remaining_hours, remark, created_time, updated_time, deleted
|
||
```
|
||
|
||
---
|
||
|
||
### 8. 优惠券表 (coupon)
|
||
|
||
检查表结构：
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '优惠券名称',
|
||
`type` TINYINT NOT NULL COMMENT '类型：1-满减券，2-折扣券，3-代金券',
|
||
`discount_type` TINYINT NOT NULL COMMENT '折扣类型：1-金额，2-折扣',
|
||
`discount_value` DECIMAL(10,2) NOT NULL COMMENT '折扣值（金额或折扣率）',
|
||
`min_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '最低消费金额',
|
||
`max_discount` DECIMAL(10,2) COMMENT '最大折扣金额',
|
||
`total_quantity` INT NOT NULL COMMENT '发行总量',
|
||
`received_quantity` INT DEFAULT 0 COMMENT '已领取数量',
|
||
`used_quantity` INT DEFAULT 0 COMMENT '已使用数量',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '结束时间',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-停用，1-启用',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_start_end_time` (`start_time`, `end_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';
|
||
```
|
||
|
||
---
|
||
|
||
### 9. 用户优惠券表 (user_coupon)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `user_coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`coupon_id` BIGINT NOT NULL COMMENT '优惠券ID',
|
||
`order_id` BIGINT COMMENT '使用的订单ID',
|
||
`status` TINYINT DEFAULT 0 COMMENT '状态：0-未使用，1-已使用，2-已过期',
|
||
`receive_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
||
`use_time` DATETIME COMMENT '使用时间',
|
||
`expire_time` DATETIME NOT NULL COMMENT '过期时间',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_coupon_id` (`coupon_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_time` (`expire_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券表';
|
||
```
|
||
|
||
---
|
||
|
||
### 10. 用户地址表 (user_address)
|
||
|
||
检查表结构：
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `user_address` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`contact_name` VARCHAR(50) NOT NULL COMMENT '联系人姓名',
|
||
`contact_phone` VARCHAR(20) NOT NULL COMMENT '联系电话',
|
||
`province` VARCHAR(50) COMMENT '省份',
|
||
`city` VARCHAR(50) COMMENT '城市',
|
||
`district` VARCHAR(50) COMMENT '区县',
|
||
`address` VARCHAR(200) NOT NULL COMMENT '详细地址',
|
||
`address_tag` VARCHAR(20) COMMENT '地址标签：家，公司，学校',
|
||
`longitude` DECIMAL(10,6) COMMENT '经度',
|
||
`latitude` DECIMAL(10,6) COMMENT '纬度',
|
||
`is_default` TINYINT DEFAULT 0 COMMENT '是否默认地址：0-否，1-是',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_is_default` (`is_default`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户地址表';
|
||
```
|
||
|
||
---
|
||
|
||
### 11. 陪伴员收益表 (teacher_salary)
|
||
|
||
检查并扩展字段：
|
||
|
||
```sql
|
||
ALTER TABLE `teacher_salary` ADD COLUMN IF NOT EXISTS `order_id` BIGINT COMMENT '订单ID';
|
||
ALTER TABLE `teacher_salary` ADD COLUMN IF NOT EXISTS `settlement_type` VARCHAR(50) DEFAULT 'order' COMMENT '结算类型：order-订单结算，manual-手动结算';
|
||
ALTER TABLE `teacher_salary` ADD COLUMN IF NOT EXISTS `income_ratio` DECIMAL(5,2) DEFAULT 70.00 COMMENT '收益比例（%）';
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `teacher_salary` ADD INDEX IF NOT EXISTS `idx_order_id` (`order_id`);
|
||
ALTER TABLE `teacher_salary` ADD INDEX IF NOT EXISTS `idx_settlement_type` (`settlement_type`);
|
||
```
|
||
|
||
---
|
||
|
||
### 12. 管理师提成表 (manager_commission) - 新建
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `manager_commission` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`manager_id` BIGINT NOT NULL COMMENT '管理师ID',
|
||
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
||
`order_amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
|
||
`commission_ratio` DECIMAL(5,2) NOT NULL DEFAULT 10.00 COMMENT '提成比例（%）',
|
||
`commission_amount` DECIMAL(10,2) NOT NULL COMMENT '提成金额',
|
||
`status` TINYINT DEFAULT 0 COMMENT '状态：0-未结算，1-已结算',
|
||
`settlement_time` DATETIME COMMENT '结算时间',
|
||
`remark` VARCHAR(500) COMMENT '备注',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_manager_id` (`manager_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_created_time` (`created_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理师提成表';
|
||
```
|
||
|
||
---
|
||
|
||
## 📋 SQL 执行脚本
|
||
|
||
将创建一个完整的 SQL 脚本文件：`payment_system_database.sql`
|
||
|
||
该脚本将包含：
|
||
1. 所有表的创建语句（IF NOT EXISTS）
|
||
2. 所有字段的添加语句（ADD COLUMN IF NOT EXISTS）
|
||
3. 所有索引的创建语句
|
||
4. 数据完整性检查
|
||
|
||
---
|
||
|
||
## 🔍 数据完整性约束
|
||
|
||
### 外键关系（逻辑外键，不使用物理外键）
|
||
|
||
```
|
||
order.timecard_id → time_card.id
|
||
order.package_id → package.id
|
||
order.coupon_id → user_coupon.id
|
||
order.service_address_id → user_address.id
|
||
|
||
wallet_transaction.wallet_id → wallet.id
|
||
wallet_transaction.order_id → order.id
|
||
|
||
time_card_usage.timecard_id → time_card.id
|
||
time_card_usage.order_id → order.id
|
||
|
||
package_usage.package_id → package.id
|
||
package_usage.order_id → order.id
|
||
|
||
user_coupon.coupon_id → coupon.id
|
||
user_coupon.order_id → order.id
|
||
|
||
teacher_salary.order_id → order.id
|
||
manager_commission.order_id → order.id
|
||
```
|
||
|
||
### 数据一致性规则
|
||
|
||
1. **钱包余额一致性**
|
||
```
|
||
wallet.balance = wallet.total_recharge - wallet.total_consume - wallet.total_withdraw
|
||
```
|
||
|
||
2. **次卡次数一致性**
|
||
```
|
||
time_card.remaining_times = time_card.total_times - time_card.used_times
|
||
```
|
||
|
||
3. **套餐课时一致性**
|
||
```
|
||
package.remaining_hours = package.total_hours - package.used_hours
|
||
```
|
||
|
||
4. **订单金额一致性**
|
||
```
|
||
order.actual_amount = order.original_amount - order.discount_amount
|
||
order.discount_amount = order.coupon_discount
|
||
```
|
||
|
||
5. **收益分配一致性**
|
||
```
|
||
order.actual_amount = order.teacher_income + order.manager_commission + order.platform_income
|
||
```
|
||
|
||
---
|
||
|
||
## 🔐 乐观锁字段
|
||
|
||
以下表使用乐观锁防止并发问题：
|
||
|
||
- `wallet.version` - 钱包余额更新
|
||
- `time_card.version` - 次卡次数更新
|
||
- `package.version` - 套餐课时更新
|
||
|
||
更新示例：
|
||
```sql
|
||
UPDATE wallet
|
||
SET balance = balance - ?, version = version + 1
|
||
WHERE id = ? AND version = ?
|
||
```
|
||
|
||
---
|
||
|
||
## 📊 索引优化
|
||
|
||
### 查询优化索引
|
||
|
||
1. **订单查询优化**
|
||
- `idx_user_id` - 按用户查询订单
|
||
- `idx_payment_status` - 按支付状态查询
|
||
- `idx_settlement_status` - 按结算状态查询
|
||
|
||
2. **钱包交易查询优化**
|
||
- `idx_user_id` - 按用户查询交易记录
|
||
- `idx_created_time` - 按时间查询交易记录
|
||
- `idx_transaction_no` - 按流水号查询
|
||
|
||
3. **优惠券查询优化**
|
||
- `idx_user_id` + `idx_status` - 查询用户可用优惠券
|
||
- `idx_expire_time` - 查询即将过期的优惠券
|
||
|
||
---
|
||
|
||
## 🧪 数据验证查询
|
||
|
||
### 1. 检查钱包余额一致性
|
||
|
||
```sql
|
||
SELECT
|
||
w.id,
|
||
w.user_id,
|
||
w.balance,
|
||
w.total_recharge,
|
||
w.total_consume,
|
||
w.total_withdraw,
|
||
(w.total_recharge - w.total_consume - w.total_withdraw) AS calculated_balance,
|
||
(w.balance - (w.total_recharge - w.total_consume - w.total_withdraw)) AS difference
|
||
FROM wallet w
|
||
WHERE w.balance != (w.total_recharge - w.total_consume - w.total_withdraw);
|
||
```
|
||
|
||
### 2. 检查次卡次数一致性
|
||
|
||
```sql
|
||
SELECT
|
||
tc.id,
|
||
tc.user_id,
|
||
tc.total_times,
|
||
tc.used_times,
|
||
tc.remaining_times,
|
||
(tc.total_times - tc.used_times) AS calculated_remaining,
|
||
(tc.remaining_times - (tc.total_times - tc.used_times)) AS difference
|
||
FROM time_card tc
|
||
WHERE tc.remaining_times != (tc.total_times - tc.used_times);
|
||
```
|
||
|
||
### 3. 检查套餐课时一致性
|
||
|
||
```sql
|
||
SELECT
|
||
p.id,
|
||
p.user_id,
|
||
p.total_hours,
|
||
p.used_hours,
|
||
p.remaining_hours,
|
||
(p.total_hours - p.used_hours) AS calculated_remaining,
|
||
(p.remaining_hours - (p.total_hours - p.used_hours)) AS difference
|
||
FROM package p
|
||
WHERE p.remaining_hours != (p.total_hours - p.used_hours);
|
||
```
|
||
|
||
---
|
||
|
||
## 📝 下一步
|
||
|
||
1. 执行 SQL 脚本创建/更新表结构
|
||
2. 验证数据完整性
|
||
3. 开始后端接口实现
|
||
|
||
---
|
||
|
||
**文档版本**: v1.0
|
||
**最后更新**: 2026-01-23
|