mysql搭建在线支付系统中的订单与支付表设计

订单表orders必须分离业务状态与支付状态，即order_status仅管理履约（如created/shipped），payment_status仅管理资金（如pending/paid）；payments表需支持多笔支付与部分退款，通过order_id+payment_no联合标识，禁用外键而用应用层校验与对账补偿；所有关键操作须幂等，依赖order_no、payment_no等唯一索引及显式时间戳。

订单表 orders 必须分离业务状态与支付状态

很多团队一开始把订单状态（如“已下单”“已发货”）和支付状态（如“待支付”“已退款”）混在同一个字段里，结果后续对账、查异常、对接支付平台时全乱套。订单的核心是锁定商品和用户意图，支付的核心是资金流转，二者生命周期不同、更新来源不同、一致性要求也不同。

• order_status 字段只管订单履约：取值如 'created'、'shipped'、'completed'、'cancelled'
• payment_status 字段只管资金：取值如 'pending'、'paid'、'refunded'、'failed'
• 不要用 status 这种模糊字段，避免后期加字段或改枚举值时引发隐性 bug
• 订单创建时，payment_status = 'pending' 是安全起点；支付回调成功后才更新它，且必须走幂等逻辑

支付表 payments 要支持多笔支付与部分退款

真实场景中，一个订单可能被分多次支付（比如定金+尾款），也可能被部分退款（比如退一半货）。如果 payments 表只存一条记录、且直接关联到 order_id，就无法表达这种关系。

• 主键用自增 id，但业务关键字段是 order_id + payment_no（支付单号，来自微信/支付宝）
• 加字段 amount（本次支付金额，单位：分），不是订单总金额
• 加字段 refunded_amount（本次支付中已退款金额，单位：分），支持部分退款追溯
• 加字段 channel（如 'alipay'、'wechat'），方便后续渠道对账和费率统计
• 索引至少建在 (order_id) 和 (payment_no) 上，查询订单所有支付记录或验签时才不慢

外键与事务边界：订单创建用事务，支付回调不能依赖外键约束

mysql 外键看似能保一致性，但在支付系统里反而容易成为故障点。支付回调是异步的、跨系统的，你无法控制对方什么时候调、调几次、网络是否超时。硬加外键会导致回调失败或死锁。

• 订单表 orders 创建时，用事务保证 INSERT INTO orders + INSERT INTO order_items 原子性
• 支付表 payments 不设 FOREIGN KEY (order_id) REFERENCES orders(id)，改用应用层校验 + 定时对账补偿
• 支付回调接口收到通知后，先查 orders 确认订单存在且未完成支付，再插入 payments 记录，最后更新 orders.payment_status —— 这三步要在一个事务里完成，但不要靠外键强制
• 如果用的是 MySQL 8.0+，可考虑给 payments.order_id 加普通索引 + 应用层兜底日志，比外键更可控

时间字段与幂等设计：每个关键操作都带唯一业务 ID

支付系统最怕重复处理。微信可能因超时重发通知，前端可能因用户连点触发多次支付请求。靠数据库唯一索引是最简单可靠的幂等手段。

• 订单表加 order_no（业务单号，如 'ORD20240520123456'），设为 UNIQUE
• 支付表加 payment_no（渠道返回的支付单号），也设为 UNIQUE
• 所有时间字段统一用 DATETIME(3)，包含毫秒，避免高并发下时间戳重复
• 加 created_at 和 updated_at，但不要用 ON UPDATE CURRENT_TIMESTAMP 自动更新 —— 支付状态变化必须显式赋值，否则查问题时不知道谁改的
• 退款操作必须生成新的 refund_no（不复用 payment_no），并关联到原 payment_id，方便追踪资金流向

CREATE TABLE orders (   id BIGINT PRIMARY KEY AUTO_INCREMENT,   order_no VARCHAR(32) NOT NULL UNIQUE,   user_id BIGINT NOT NULL,   total_amount INT NOT NULL COMMENT '单位：分',   order_status ENUM('created','shipped','completed','cancelled') NOT NULL DEFAULT 'created',   payment_status ENUM('pending','paid','refunded','failed') NOT NULL DEFAULT 'pending',   created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),   updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) );  CREATE TABLE payments (   id BIGINT PRIMARY KEY AUTO_INCREMENT,   order_id BIGINT NOT NULL,   payment_no VARCHAR(64) NOT NULL UNIQUE,   channel VARCHAR(16) NOT NULL,   amount INT NOT NULL COMMENT '单位：分',   refunded_amount INT NOT NULL DEFAULT 0,   created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),   INDEX idx_order_id (order_id),   INDEX idx_payment_no (payment_no) );

实际跑起来之后，最容易被忽略的是 refunded_amount 的累加逻辑和 payment_status 的最终态判断 —— 很多人以为“只要有一笔 paid 就算支付成功”，但没考虑部分退款后是否仍算有效支付。这个边界得由业务规则明确定义，数据库只负责存准、查快。