设计涉及金钱的数据库系统时，核心原则是：准确性（Precision）、一致性（Consistency）和可追溯性（Auditability）。 绝对不能出现资金凭空消失或浮点数计算误差。

以下是行业通用的标准设计方案，包含表结构设计、核心逻辑和关键注意事项。

1. 核心表结构设计

通常建议将“用户主表”与“钱包表”分离，同时必须有一个“流水表”来记录每一笔变动。

A. 钱包表 (user_wallets)

用于存储当前余额。

• 关键点：使用 DECIMAL 类型，严禁使用 FLOAT 或 DOUBLE。

CREATE TABLE `user_wallets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户ID',

  -- 重点：金额字段
  -- DECIMAL(18, 4) 表示总共18位，小数占4位。
  -- 即使业务只需要2位小数，多留几位可以防止汇率换算等中间运算的精度丢失。
  `balance` decimal(18, 4) NOT NULL DEFAULT '0.0000' COMMENT '可用余额',
  `frozen_balance` decimal(18, 4) NOT NULL DEFAULT '0.0000' COMMENT '冻结余额(用于提现中/交易中)',

  -- 乐观锁版本号，处理并发关键
  `version` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '并发控制版本号',

  `pay_password` varchar(255) DEFAULT NULL COMMENT '支付密码',
  `status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '状态 1:正常 0:冻结',
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户钱包表';

B. 账户流水表 (wallet_logs / transactions)

用于记录每一次余额变化的原因。

• 关键点：数据一旦写入，不可修改，不可删除。

CREATE TABLE `wallet_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户ID',

  -- 变动方向：1 收入, 2 支出
  `direction` tinyint(1) NOT NULL COMMENT '变动方向', 

  -- 变动金额 (绝对值)
  `amount` decimal(18, 4) NOT NULL COMMENT '变动金额',

  -- 变动前/后的余额快照 (非常重要，用于对账)
  `before_balance` decimal(18, 4) NOT NULL COMMENT '变动前余额',
  `after_balance` decimal(18, 4) NOT NULL COMMENT '变动后余额',

  -- 业务关联
  `business_type` varchar(32) NOT NULL COMMENT '业务类型: order_pay, recharge, refund, withdraw',
  `business_id` varchar(64) NOT NULL COMMENT '关联业务单号(订单号/充值单号)',

  -- 备注
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',

  -- 幂等性Key (防止重复扣款)
  `deduplication_key` varchar(128) NOT NULL COMMENT '唯一去重键(业务类型+业务ID)',

  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  UNIQUE KEY `uk_dedup` (`deduplication_key`) -- 数据库层面的防重
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='钱包变动流水表';

2. 核心操作逻辑（非常重要）

在进行余额扣减或增加时，必须遵守以下逻辑：

A. 必须使用数据库事务 (Transaction)

余额更新和流水写入必须在同一个事务中，要么都成功，要么都失败。

B. 并发控制（防止“超扣”）

当两个请求同时到达（例如用户余额100，同时发起了两笔100元的支付），需要防止余额变成 -100。

方案一：悲观锁 (Pessimistic Locking) 适用于高并发冲突较多的场景，或者强一致性要求极高的场景。

START TRANSACTION;

-- 1. 锁定该行记录
SELECT balance FROM user_wallets WHERE user_id = 1 FOR UPDATE;

-- 2. 应用层判断余额是否充足
-- if (balance < need_amount) { ROLLBACK; return error; }

-- 3. 更新余额
UPDATE user_wallets SET balance = balance - 100 WHERE user_id = 1;

-- 4. 写入流水
INSERT INTO wallet_logs (...) VALUES (...);

COMMIT;

方案二：乐观锁 (Optimistic Locking / CAS) 适用于大多数互联网场景，性能更好。利用 SQL 的原子性更新。

-- 不需要 select for update

-- 直接尝试更新，带上条件
UPDATE user_wallets 
SET 
    balance = balance - 100, 
    version = version + 1 
WHERE 
    user_id = 1 
    AND balance >= 100; -- 关键：在数据库层判断余额是否足够

-- 检查 affected_rows (受影响行数)
-- 如果为 1：说明扣款成功，继续写入流水表。
-- 如果为 0：说明余额不足，或者并发冲突，返回失败。

3. 特殊场景设计

A. 冻结资金（Transaction Pending）

场景：用户提现申请，或者参与拍卖缴纳保证金。钱还在账户里，但不能用。

• 逻辑：
  1. balance 减去 100，frozen_balance 加上 100。
  2. 记录一条类型为“冻结”的流水。
• 成交后：
  1. frozen_balance 减去 100。
  2. 记录一条类型为“支出”的流水。
• 失败/解冻：
  1. frozen_balance 减去 100，balance 加上 100。
  2. 记录一条类型为“解冻”的流水。

B. 幂等性设计 (Idempotency)

网络抖动可能导致前端发了两次请求，或者支付回调了两次。

• 利用 wallet_logs 中的 deduplication_key。
• Key 的生成规则通常是：source_type + order_id。
• 如果重复插入相同的 key，数据库会报 Duplicate entry 错误，利用这个错误来拦截重复操作。

4. 常见误区总结

下一步建议

如果您正在编写具体的业务代码，我可以为您提供一段基于 PHP + MySQL (PDO) 的原子性扣款代码示例。您需要吗？