mysql如何设计用户积分兑换系统_mysql积分表设计与逻辑

来源：这里教程网时间：2026-02-28 20:49:04 作者：

积分表必须拆成用户积分主表 + 兑换流水表

直接在

users

表里加一个

points

字段看似简单，但会引发并发扣减不一致、无法追溯、审计困难等问题。真实业务中，积分变动必须可查、可逆、可对账。

推荐两张表结构：

CREATE TABLE user_points (
  user_id BIGINT NOT NULL PRIMARY KEY,
  total_points BIGINT NOT NULL DEFAULT 0,
  frozen_points BIGINT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_updated_at (updated_at)
);

CREATE TABLE points_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount BIGINT NOT NULL, -- 正数为增加，负数为扣除
  biz_type VARCHAR(20) NOT NULL, -- 'login', 'order', 'exchange', 'refund'
  biz_id VARCHAR(64), -- 关联业务单号，如 order_no 或 exchange_id
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_biz (biz_type, biz_id)
);

total_points
是用户当前可用总积分，
frozen_points
用于临时冻结（比如兑换下单但未支付完成）所有积分变动必须写入
points_log
，禁止绕过日志直接更新
user_points

biz_id
必须填，否则后续对账时根本找不到这笔积分对应的业务源头

兑换操作必须用事务 + 乐观锁防超兑

用户点击“确认兑换”时，不能只靠应用层查一次余额再扣减——高并发下极易超兑。必须在数据库层面做原子校验。

推荐写法（以兑换商品为例）：

START TRANSACTION;
-- 1. 检查并锁定该用户积分记录（防止并发修改）
SELECT total_points, frozen_points FROM user_points 
WHERE user_id = 123 FOR UPDATE;
<p>-- 2. 应用层判断：total_points - frozen_points >= required_points ?
-- 3. 若通过，则更新：
UPDATE user_points 
SET total_points = total_points - 500,
frozen_points = frozen_points + 500 
WHERE user_id = 123 
AND total_points - frozen_points >= 500;</p><p>-- 4. 检查影响行数是否为 1；若为 0，说明余额不足或已被其他事务抢占，回滚
-- 5. 插入日志（注意 amount = -500）
INSERT INTO points_log (user_id, amount, biz_type, biz_id, remark) 
VALUES (123, -500, 'exchange', 'EX20240520001', '兑换咖啡券');
COMMIT;

不要用
SELECT ... FOR UPDATE
后再
UPDATE
两次，容易因网络延迟或异常导致状态不一致把余额校验和扣减合并进同一个
UPDATE
的
WHERE
条件里，是 MySQL 最可靠的防超兑手段冻结逻辑要配套：支付成功后调
UPDATE user_points SET frozen_points = frozen_points - 500
，失败则解冻

历史积分失效（过期）别用定时任务硬删

很多团队用每天凌晨跑

DELETE FROM points_log WHERE expired_at ，结果大表锁表、主从延迟飙升。积分过期不是数据删除问题，而是「是否计入可用余额」的计算逻辑问题。

正确做法：

在
points_log
加字段
expire_at DATETIME NULL
，记录每笔积分的过期时间 查可用余额时，改用聚合查询（或物化视图/冗余字段）：

SELECT 
  COALESCE(SUM(CASE WHEN expire_at IS NULL OR expire_at > NOW() THEN amount ELSE 0 END), 0) AS available_points
FROM points_log 
WHERE user_id = 123;

如果性能扛不住，就在
user_points
里加
available_points
字段，每次写日志后异步更新它（用消息队列或延迟任务） 真正要清理的，只是已过期且无业务关联的旧日志，可以按月分表或归档，而不是每天硬删