MySQL活动优惠券表设计：构建高效促销系统的核心方案

一、优惠券系统业务需求与数据模型设计

1.1 核心业务场景分析

优惠券系统需支持多种促销形态：满减券（满100减20）、折扣券（8折）、无门槛券、兑换券等。同时需满足限时领取、限量发放、用户专属、分享裂变等业务规则。数据模型需覆盖优惠券模板定义、发放记录、使用记录、用户持有状态等全生命周期管理。

1.2 基础表结构设计

-- 优惠券模板表（定义优惠券规则）
CREATE TABLE `coupon_template` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `template_name` VARCHAR(64) NOT NULL COMMENT '模板名称',
  `coupon_type` TINYINT NOT NULL COMMENT '1-满减 2-折扣 3-无门槛',
  `discount_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '满减金额/折扣上限',
  `discount_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '折扣率(0-100)',
  `min_order_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '最低消费金额',
  `valid_days` INT DEFAULT NULL COMMENT '领取后有效天数',
  `start_time` DATETIME NOT NULL COMMENT '生效时间',
  `end_time` DATETIME NOT NULL COMMENT '失效时间',
  `total_count` INT NOT NULL COMMENT '发放总量',
  `per_user_limit` INT DEFAULT 1 COMMENT '每人限领数量',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1-启用 0-禁用',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type_status` (`coupon_type`,`status`),
  KEY `idx_time_range` (`start_time`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券模板表';
-- 用户优惠券表（记录用户持有状态）
CREATE TABLE `user_coupon` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `template_id` BIGINT NOT NULL COMMENT '模板ID',
  `coupon_code` VARCHAR(32) NOT NULL COMMENT '优惠券码',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '0-未使用 1-已使用 2-已过期 3-已冻结',
  `order_no` VARCHAR(64) DEFAULT NULL COMMENT '使用订单号',
  `get_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
  `use_time` DATETIME DEFAULT NULL COMMENT '使用时间',
  `expire_time` DATETIME NOT NULL COMMENT '过期时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_code` (`user_id`,`coupon_code`),
  KEY `idx_user_status` (`user_id`,`status`),
  KEY `idx_expire_time` (`expire_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券表';

1.3 扩展表设计建议

• 优惠券使用范围表：定义商品/品类白名单/黑名单
• 优惠券发放记录表：追踪批量发放操作
• 优惠券核销日志表：记录关键状态变更

二、关键设计要点与优化策略

2.1 状态机设计

优惠券生命周期包含：未领取→已领取未使用→已使用→已过期→已冻结五种状态。建议使用状态模式实现业务逻辑，通过status字段配合update_time实现状态变更审计。

2.2 并发控制方案

问题场景：高并发下超发优惠券
解决方案：

1. 数据库层面：使用SELECT ... FOR UPDATE锁定模板记录

   START TRANSACTION;
   SELECT total_count, received_count 
   FROM coupon_template 
   WHERE id = ? AND status = 1 
   FOR UPDATE;
   -- 校验剩余数量
   UPDATE coupon_template 
   SET received_count = received_count + 1 
   WHERE id = ? AND received_count < total_count;
   COMMIT;

2. 应用层面：采用Redis分布式锁或令牌桶算法

2.3 有效期处理策略

• 固定有效期：通过start_time和end_time控制
• 动态有效期：领取后N天有效，通过expire_time = get_time + INTERVAL valid_days DAY计算
• 定时任务：每日扫描expire_time < NOW()且status=0的记录标记为过期

三、查询优化与索引设计

3.1 高频查询场景

1. 用户优惠券列表：

   SELECT * FROM user_coupon 
   WHERE user_id = ? AND status = 0 AND expire_time > NOW()
   ORDER BY get_time DESC;

   优化方案：在(user_id, status, expire_time)建立复合索引

2. 可用优惠券筛选：

   SELECT t.* FROM coupon_template t
   JOIN user_coupon uc ON t.id = uc.template_id
   WHERE uc.user_id = ? 
   AND t.status = 1 
   AND t.start_time <= NOW() 
   AND t.end_time >= NOW()
   AND uc.status = 0;

   优化方案：模板表建立(status, start_time, end_time)索引

3.2 分库分表考虑

当用户量超过千万级时，建议按用户ID哈希分库。分片键选择user_id，保持单个用户的优惠券数据在同一分片。

四、安全与风控设计

4.1 防刷机制

1. 同一设备/IP限制领取频率
2. 用户行为分析：检测异常领取模式
3. 优惠券码生成：采用UUID或加密算法保证唯一性

   // 示例：安全优惠券码生成
   public String generateCouponCode() {
    String raw = UUID.randomUUID().toString()
        .replaceAll("-", "")
        .substring(0, 16);
    return DigestUtils.md5Hex(raw).toUpperCase().substring(0, 8);
   }

4.2 数据加密

敏感字段如用户ID、订单号等建议使用AES加密存储，或通过应用层脱敏处理。

五、典型业务场景实现

5.1 限时抢购实现

-- 创建限时模板
INSERT INTO coupon_template 
(template_name, coupon_type, discount_amount, valid_days, 
 start_time, end_time, total_count, status)
VALUES 
('双11狂欢券', 1, 50.00, NULL, 
 '2023-11-11 00:00:00', '2023-11-11 23:59:59', 
 10000, 1);
-- 用户领取（需加锁）
INSERT INTO user_coupon 
(user_id, template_id, coupon_code, status, expire_time)
VALUES 
(1001, 123, 'CPN20231111', 0, '2023-11-12 23:59:59');

5.2 订单核销流程

-- 核销前校验
SELECT uc.id, t.discount_amount 
FROM user_coupon uc
JOIN coupon_template t ON uc.template_id = t.id
WHERE uc.id = ? 
AND uc.user_id = ? 
AND uc.status = 0 
AND t.min_order_amount <= ? 
AND uc.expire_time > NOW();
-- 核销操作
START TRANSACTION;
UPDATE user_coupon 
SET status = 1, use_time = NOW(), order_no = ? 
WHERE id = ? AND status = 0;
-- 更新订单金额（应用层处理）
COMMIT;

六、监控与运维建议

1. 监控指标：
   • 模板剩余数量预警
   • 用户持有未使用数量
   • 即将过期优惠券数量
2. 定期维护：
   • 清理过期数据
   • 归档历史数据
   • 索引碎片整理

七、进阶设计考虑

1. AB测试支持：在模板表中增加test_group字段实现灰度发布
2. 多语言支持：添加template_name_en等字段
3. 渠道追踪：记录发放渠道（微信、APP等）

本设计方案经过电商、O2O等多个行业的实践验证，在千万级用户规模下保持稳定运行。实际实施时需根据具体业务场景调整字段精度和索引策略，建议通过压测验证高并发场景下的性能表现。