MySQL活动优惠券表设计：从基础到进阶的完整指南

2025年11月6日 互联网

一、核心表结构设计

优惠券系统作为电商、O2O等业务的核心模块，其表结构设计直接影响业务灵活性与系统性能。以下从基础表结构出发，逐步深入各关键设计点。

1.1 基础表结构定义

  1. CREATE TABLE `coupon` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '优惠券ID',
  3.   `coupon_code` varchar(32) NOT NULL COMMENT '优惠券编码',
  4.   `coupon_type` tinyint(4) NOT NULL COMMENT '类型：1-折扣券 2-满减券 3-现金券',
  5.   `discount_value` decimal(10,2) DEFAULT NULL COMMENT '折扣值/减免金额',
  6.   `min_order_amount` decimal(10,2) DEFAULT NULL COMMENT '最低消费金额',
  7.   `start_time` datetime NOT NULL COMMENT '生效时间',
  8.   `end_time` datetime NOT NULL COMMENT '失效时间',
  9.   `total_count` int(11) NOT NULL COMMENT '总发放量',
  10.   `used_count` int(11) DEFAULT '0' COMMENT '已使用量',
  11.   `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态：0-禁用 1-启用',
  12.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  13.   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  14.   PRIMARY KEY (`id`),
  15.   UNIQUE KEY `uk_coupon_code` (`coupon_code`),
  16.   KEY `idx_time_range` (`start_time`,`end_time`),
  17.   KEY `idx_status` (`status`)
  18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券基础表';

该设计包含核心字段：

  • 类型区分：通过coupon_type实现不同优惠形式的统一管理
  • 时间范围：start_time/end_time支持精确的时间有效性控制
  • 库存控制：total_count与used_count实现发放量限制
  • 状态管理：status字段支持运营快速上下架

1.2 用户优惠券关系表

  1. CREATE TABLE `user_coupon` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  4.   `coupon_id` bigint(20) NOT NULL COMMENT '优惠券ID',
  5.   `order_id` bigint(20) DEFAULT NULL COMMENT '关联订单ID',
  6.   `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-未使用 1-已使用 2-已过期',
  7.   `get_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '获取时间',
  8.   `use_time` datetime DEFAULT NULL COMMENT '使用时间',
  9.   `expire_time` datetime NOT NULL COMMENT '过期时间',
  10.   PRIMARY KEY (`id`),
  11.   UNIQUE KEY `uk_user_coupon` (`user_id`,`coupon_id`),
  12.   KEY `idx_user_status` (`user_id`,`status`),
  13.   KEY `idx_expire_time` (`expire_time`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券关系表';

关键设计点：

  • 唯一约束：防止用户重复领取同一优惠券
  • 状态追踪：细粒度状态管理支持运营分析
  • 过期预警：expire_time索引支持即将过期优惠券的提醒

二、业务规则实现方案

2.1 复杂优惠规则实现

针对”满300减50，限服饰类目”等复合规则，建议采用JSON字段存储规则：

  1. ALTER TABLE `coupon` 
  2. ADD COLUMN `rule_config` json DEFAULT NULL COMMENT '规则配置';

示例规则数据：

  1. {
  2.   "category_limits": ["101","102"],
  3.   "brand_limits": ["B001","B002"],
  4.   "user_level_limits": [3,4],
  5.   "daily_limit": 2,
  6.   "weekly_limit": 5
  7. }

业务层可通过JSON函数解析规则：

  1. SELECT * FROM coupon 
  2. WHERE JSON_CONTAINS(rule_config->'$.category_limits', '"101"')
  3. AND now() BETWEEN start_time AND end_time;

2.2 库存控制策略

方案一：预扣减模式（推荐）

  1. -- 领取时预扣减
  2. UPDATE coupon 
  3. SET used_count = used_count + 1 
  4. WHERE id = ? AND (used_count < total_count);
  6. -- 事务中回滚处理
  7. START TRANSACTION;
  8. -- 1. 预扣减库存
  9. UPDATE coupon SET used_count = used_count + 1 WHERE ...;
  10. -- 2. 插入用户券记录
  11. INSERT INTO user_coupon ...;
  12. COMMIT;

方案二：实时计算模式

  1. -- 查询时动态计算剩余量
  2. SELECT 
  3.   id, 
  4.   total_count - (SELECT COUNT(*) FROM user_coupon WHERE coupon_id = c.id) as remaining
  5. FROM coupon c
  6. WHERE c.id = ?;

三、性能优化实践

3.1 索引优化策略

  1. 复合索引设计：

    • 查询场景：”查找用户未使用的有效券” 
      1. ALTER TABLE user_coupon 
      2. ADD INDEX idx_user_valid (user_id, status, expire_time);

  2. 覆盖索引应用：

    1. -- 优化前
    2. SELECT * FROM coupon WHERE id = ?;
    4. -- 优化后（只查询必要字段）
    5. SELECT id,coupon_type,discount_value FROM coupon WHERE id = ?;

3.2 分表分库方案

当数据量超过千万级时，建议按用户ID分表：

  1. -- 动态表名示例
  2. SELECT * FROM user_coupon_${user_id%10} 
  3. WHERE user_id = ? AND status = 0;

四、高并发场景处理

4.1 库存超卖解决方案

方案一：数据库乐观锁

  1. UPDATE coupon 
  2. SET used_count = used_count + 1 
  3. WHERE id = ? AND used_count < total_count AND version = ?;

方案二：Redis分布式锁

  1. // Java示例
  2. String lockKey = "coupon_lock_" + couponId;
  3. try {
  4.     RLock lock = redissonClient.getLock(lockKey);
  5.     lock.lock(10, TimeUnit.SECONDS);
  6.     // 执行库存扣减
  7. } finally {
  8.     lock.unlock();
  9. }

4.2 缓存预热策略

  1. 活动开始前预热：

    1. -- 提前将活动券加载到Redis
    2. INSERT INTO redis_coupon_cache 
    3. SELECT * FROM coupon 
    4. WHERE start_time > NOW() AND start_time < DATE_ADD(NOW(), INTERVAL 1 HOUR);

  2. 多级缓存架构：

    1. LocalCache → Redis → MySQL

五、监控与运维建议

5.1 关键指标监控

  1. 库存预警：

    1. SELECT id, total_count, used_count 
    2. FROM coupon 
    3. WHERE total_count - used_count < total_count * 0.1;

  2. 过期券统计：

    1. SELECT COUNT(*) as expired_count 
    2. FROM user_coupon 
    3. WHERE expire_time < NOW() AND status = 0;

5.2 定期维护任务

  1. 清理过期数据：

    1. -- 每月执行
    2. DELETE FROM user_coupon 
    3. WHERE expire_time < DATE_SUB(NOW(), INTERVAL 3 MONTH) 
    4. AND status IN (1,2);

  2. 索引重建：

    1. ANALYZE TABLE coupon, user_coupon;
    2. OPTIMIZE TABLE user_coupon_0, user_coupon_1; -- 分表优化

六、扩展性设计

6.1 多活动类型支持

  1. CREATE TABLE `promotion_activity` (
  2.   `id` bigint(20) NOT NULL,
  3.   `activity_type` tinyint(4) NOT NULL COMMENT '1-优惠券 2-抽奖 3-拼团',
  4.   `config` json NOT NULL COMMENT '活动配置',
  5.   PRIMARY KEY (`id`)
  6. );

6.2 跨平台优惠券设计

  1. ALTER TABLE coupon 
  2. ADD COLUMN `platform_type` tinyint(4) DEFAULT '0' COMMENT '0-全平台 1-APP 2-H5 3-小程序';

通过以上设计，系统可支持：

  • 每日百万级优惠券发放
  • 毫秒级优惠券查询响应
  • 复杂业务规则的灵活配置
  • 完善的监控运维体系

实际开发中，建议结合具体业务场景进行适当调整，并通过压力测试验证设计合理性。对于超大规模系统，可考虑引入分库分表中间件（如ShardingSphere）或NewSQL数据库解决方案。

最新文章
热门标签