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

乐彼多用户商城系统LBMall(.net)

乐彼多用户商城系统，采用ASP.NET分层技术和AJAX技术，运营于高速稳定的微软.NET+MSSQL 2005平台；完全具备搭建超大型网络购物多用户网上商城的整体技术框架和应用层次LBMall 秉承乐彼软件优秀品质，后台人性化设计，管理窗口识别客户端分辨率自动调整，独立配置的菜单操作锁，使管理操作简单便捷。待办事项1、新订单、支付、付款、短信提醒2、每5分钟自动读取3、新事项声音提醒 店铺管理1

下载

• 订单表 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)
);