在 MySQL 中为买菜系统设计优惠券表的完整教程-猿码集

1. 业务目标与设计原则

在买菜系统中，优惠券是提升用户转化和复购的重要工具，设计一个可扩展、易维护的优惠券表能显著降低后期维护成本。本文围绕 temperature=0.6在 MySQL 中为买菜系统设计优惠券表的完整教程展开，旨在从需求分析、数据建模到实现落地，给出可落地的数据库设计与实现方案。

本节聚焦明确的设计目标：高可用性、强一致性、易扩展性，以及对大量并发领取和使用场景的容错能力。我们还强调将优惠券的适用范围、时间约束、领用与使用记录进行清晰分离，以便后续业务规则变更时不影响核心数据结构。

通过以下原则实现稳定落地：最小化冗余、清晰的外键约束、合理的索引、可追溯的日志表，以及在事务边界内完成领取与使用的原子性操作。

2. 数据库设计总体思路

2.1 正则化与命名规范

采用三范式思路，将优惠券主信息、适用范围、领取记录和使用记录分离成独立表，避免字段膨胀造成的更新异常。命名统一采用下划线命名法，确保跨团队协作的一致性。命名规范与字段类型选择将直接影响后续的维护成本和可读性。

另外，要将时间字段统一为可比较的时间戳类型（如 DATETIME/ TIMESTAMP），并对开始时间、结束时间、状态进行明确的业务约束，以便在查询时快速筛选有效优惠券。

在技术栈层面，优先使用 InnoDB 引擎、UTF8MB4 字符集、以及显式的外键约束，有助于保证数据的一致性和可扩展性。

3. 具体表结构设计

3.1 优惠券主表（coupons）

主表用于存放优惠券的核心信息，例如类型、金额、有效期等。通过唯一的 code 字段实现防重和快速查找，同时设定 总量与单用户领取上限，以支撑多种促销策略。

在设计时重点关注：时间段约束、状态机（ACTIVE、PAUSED、EXPIRED）、以及金额规模的合理取值范围。下面给出完整建表示例，便于在买菜系统中直接落地。

CREATE TABLE coupons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(64) NOT NULL,
  type ENUM('PERCENT','AMOUNT') NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  min_order_value DECIMAL(10,2) NOT NULL DEFAULT 0,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  status ENUM('ACTIVE','PAUSED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
  total_quantity INT UNSIGNED NOT NULL DEFAULT 0,
  per_user_limit INT UNSIGNED NOT NULL DEFAULT 1,
  used_quantity BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 优惠券适用范围表（coupon_applicable）

并非所有优惠券都对所有商品生效，因此需要一个灵活的映射表来描述适用范围。设计时采用 target_type 与 target_id 的组合，支持 ALL、CATEGORY、PRODUCT 三种类型，方便未来扩展到商家自定义的维度。

该表通过外键关联 coupons，确保在 Coupons 删除时能级联清理相关映射，同时添加组合索引以提升查询性能。

CREATE TABLE coupon_applicable (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  coupon_id BIGINT UNSIGNED NOT NULL,
  target_type ENUM('ALL','CATEGORY','PRODUCT') NOT NULL DEFAULT 'ALL',
  target_id BIGINT UNSIGNED,
  PRIMARY KEY (id),
  FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
  KEY idx_coupon_target (coupon_id, target_type, target_id),
  KEY idx_coupon_end (coupon_id, target_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.3 用户领取记录表（coupon_user_claims）

为实现 per-user 限领、防止重复领取，需要单独的领取记录表。该表记录用户对某张优惠券的领取行为，并以联合唯一键防止同一用户重复领取同一券。

该表还便于统计某段时间内的领取热度，以及对异常领取进行审计。

CREATE TABLE coupon_user_claims (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  coupon_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  claimed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_coupon (coupon_id, user_id),
  FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.4 优惠券使用记录表（coupon_usages）

使用记录表用于追踪每一次实际使用的场景，包含订单信息、使用时间等字段，便于对促销效果进行分析并支持账务对账。

通过独立的使用记录表，可以在需要时回溯某次消费对应的优惠额度、实际抵扣金额等信息。下面给出创建语句。

CREATE TABLE coupon_usages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  coupon_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  amount DECIMAL(10,2) NOT NULL,
  used_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. 常用查询与使用场景

4.1 检查可用性（领取前校验）

在用户 attempting 领取优惠券之前，需要综合判断券的有效性、时段、剩余数量以及单用户的领取上限。通过以下查询，可以快速判断某张券是否可用以及用户是否已达到领取上限。

关键点在于组合条件的快速过滤与并发控制。

-- 检查券是否 ACTIVE 且在有效期内，且库存充足
SELECT id, code, type, value, start_time, end_time
FROM coupons
WHERE id = ? AND status = 'ACTIVE'
  AND NOW() BETWEEN start_time AND end_time
  AND (total_quantity = 0 OR used_quantity < total_quantity);

-- 检查用户是否已领取过该券，若有则按 per_user_limit 限制
SELECT COUNT(*) AS claimed
FROM coupon_user_claims
WHERE coupon_id = ? AND user_id = ?;

4.2 应用范围判断与组合查询

根据 coupon_applicable 的映射，决定券对当前购物车中的商品是否生效。需要考虑 ALL、CATEGORY、PRODUCT 三种情况的组合查询。使用 JOIN 结合 WHERE 条件，可以在单一查询中完成判断。

SELECT cu.id, cu.code, cu.type, cu.value
FROM coupons cu
JOIN coupon_applicable ca ON cu.id = ca.coupon_id
WHERE cu.id = ?
  AND ca.target_type IN ('ALL', 'CATEGORY', 'PRODUCT')
  AND (
       ca.target_type = 'ALL'
       OR (ca.target_type = 'CATEGORY' AND ca.target_id IN (?))  -- category_id 集合
       OR (ca.target_type = 'PRODUCT' AND ca.target_id IN (?) )
      );

4.3 计算最终价格中的优惠金额

根据 coupon 的类型，计算最终应抵扣的金额。推荐在应用层进行初步校验后，再由数据库计算折扣金额，避免前后端不同步导致的错误。以下示例展示两种类型的抵扣逻辑。

-- 假设订单金额为 order_total，券为 coupon_id
SELECT
  CASE WHEN cu.type = 'PERCENT'
       THEN ROUND(order_total * cu.value / 100, 2)
       WHEN cu.type = 'AMOUNT'
       THEN cu.value
  END AS discount_amount,
  ROUND(order_total - (
       CASE WHEN cu.type = 'PERCENT'
            THEN order_total * cu.value / 100
            WHEN cu.type = 'AMOUNT'
            THEN cu.value
       END
     ), 2) AS final_price
FROM coupons cu
WHERE cu.id = ? AND NOW() BETWEEN cu.start_time AND cu.end_time
  AND cu.status = 'ACTIVE';

5. 性能优化与维护

5.1 索引设计

对经常筛选的字段与联表条件添加索引，将显著提升查询性能。推荐的索引有：code 的唯一索引、start_time/end_time 的范围查询索引、以及 coupon_id、user_id 组合索引在领取与使用日志中的应用。此外，对 coupon_applicable 的 (coupon_id, target_type, target_id) 组合索引有助于快速定位适用范围。

需要注意避免过多冗余索引导致写入性能下降，应结合实际并发量与查询模式进行权衡。

通过合理的分区策略与归档历史数据，也能提高长期的查询性能与维护性。

6. 实践脚本与完整建表

6.1 完整建表脚本

将前述表结构整合成一个完整的建表脚本，便于在新的环境中快速部署，确保外键关系与约束的一致性。

-- 表结构已在前文分段给出，这里给出整合后的演示脚本要点
-- 1) 创建 coupons
CREATE TABLE coupons (...);

-- 2) 创建 coupon_applicable
CREATE TABLE coupon_applicable (...);

-- 3) 创建 coupon_user_claims
CREATE TABLE coupon_user_claims (...);

-- 4) 创建 coupon_usages
CREATE TABLE coupon_usages (...);

-- 5) 视情况添加触发器/存储过程用于自动更新 used_quantity
-- 6) 根据实际业务添加必要的索引

6.2 示例数据与使用流程

以下示例演示一个完整的使用流程：创建优惠券、绑定适用范围、用户领取、用户在下单时使用，以及系统记录使用日志。请根据实际业务将示例替换为真实数据。

-- 插入一个百分比券，满减前提
INSERT INTO coupons (code, type, value, min_order_value, start_time, end_time, status, total_quantity, per_user_limit)
VALUES ('BUYCA_FEAST_10', 'PERCENT', 10.00, 50.00, NOW(), NOW() + INTERVAL 30 DAY, 'ACTIVE', 1000, 1);

-- 绑定适用范围（所有商品）
INSERT INTO coupon_applicable (coupon_id, target_type, target_id) VALUES (LAST_INSERT_ID(), 'ALL', NULL);

-- 用户领取
INSERT INTO coupon_user_claims (coupon_id, user_id) VALUES (LAST_INSERT_ID(), 12345);

-- 使用记录（假设下单）
INSERT INTO coupon_usages (coupon_id, user_id, order_id, amount) VALUES (LAST_INSERT_ID(), 12345, 88888, 0.00);

-- 更新使用数量（需在真实应用中通过触发器或应用逻辑完成）
UPDATE coupons SET used_quantity = used_quantity + 1 WHERE id = LAST_INSERT_ID();

通过以上结构与流程，买菜系统能够实现灵活多样的促销策略，同时保留完整的审计轨迹，方便后续分析与对账。

在 MySQL 中为买菜系统设计优惠券表的完整教程