本文目录导读：
设计一个完善的PHP优惠券系统，需要从数据模型、核销逻辑、并发控制等多个维度考虑,以下是一套经过实战检验的设计方案：
核心数据表设计
优惠券模板表（coupon_templates）
CREATE TABLE `coupon_templates` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL COMMENT '优惠券名称', `type` tinyint(1) NOT NULL COMMENT '1-满减券 2-折扣券 3-无门槛券', `value` decimal(10,2) NOT NULL COMMENT '面值/折扣率', `min_amount` decimal(10,2) DEFAULT '0.00' COMMENT '满减条件', `max_discount` decimal(10,2) DEFAULT NULL COMMENT '最大优惠金额(折扣券用)', `total_quantity` int(11) NOT NULL COMMENT '发行总量', `issued_quantity` int(11) DEFAULT '0' COMMENT '已发放数量', `per_user_limit` int(11) DEFAULT '1' COMMENT '每人限领数量', `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `status` tinyint(1) DEFAULT '1' COMMENT '1-启用 0-禁用', `created_at` timestamp, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
用户优惠券表（user_coupons）
CREATE TABLE `user_coupons` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `template_id` int(11) NOT NULL COMMENT '模板ID', `code` varchar(32) NOT NULL COMMENT '优惠券编码(唯一)', `status` tinyint(1) DEFAULT '0' COMMENT '0-未使用 1-已使用 2-已过期 3-已锁定', `received_at` datetime NOT NULL COMMENT '领取时间', `used_at` datetime DEFAULT NULL COMMENT '使用时间', `order_id` int(11) DEFAULT NULL COMMENT '使用的订单ID', `expire_time` datetime NOT NULL COMMENT '过期时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_code` (`code`), KEY `idx_user_status` (`user_id`, `status`), KEY `idx_expire` (`expire_time`) ) ENGINE=InnoDB;
核心PHP逻辑实现
优惠券领取（含并发控制）
class CouponService
{
/**
* 领取优惠券
*/
public function receiveCoupon(int $userId, int $templateId): array
{
$pdo = $this->getPdo();
try {
$pdo->beginTransaction();
// 1. 锁定模板行，防止超发
$templateSql = "SELECT * FROM coupon_templates WHERE id = ? FOR UPDATE";
$stmt = $pdo->prepare($templateSql);
$stmt->execute([$templateId]);
$template = $stmt->fetch();
if (!$template || $template['status'] != 1) {
throw new \Exception('优惠券不存在或已下架');
}
// 2. 检查发放数量
if ($template['issued_quantity'] >= $template['total_quantity']) {
throw new \Exception('优惠券已被抢光');
}
// 3. 检查每人限领
$limitSql = "SELECT COUNT(*) FROM user_coupons WHERE user_id = ? AND template_id = ?";
$stmt = $pdo->prepare($limitSql);
$stmt->execute([$userId, $templateId]);
$receivedCount = $stmt->fetchColumn();
if ($receivedCount >= $template['per_user_limit']) {
throw new \Exception('已达每人限领数量');
}
// 4. 生成优惠券编码(唯一)
$code = $this->generateUniqueCode($pdo);
// 5. 计算过期时间
$expireTime = date('Y-m-d H:i:s', min(
strtotime($template['end_time']),
strtotime('+30 days') // 可领取后30天有效
));
// 6. 插入领取记录
$insertSql = "INSERT INTO user_coupons
(user_id, template_id, code, status, received_at, expire_time)
VALUES (?, ?, ?, 0, NOW(), ?)";
$stmt = $pdo->prepare($insertSql);
$stmt->execute([$userId, $templateId, $code, $expireTime]);
// 7. 更新已发放数量
$updateSql = "UPDATE coupon_templates SET issued_quantity = issued_quantity + 1
WHERE id = ? AND issued_quantity < total_quantity";
$stmt = $pdo->prepare($updateSql);
$result = $stmt->execute([$templateId]);
if ($result && $stmt->rowCount() == 0) {
throw new \Exception('优惠券已被抢光');
}
$pdo->commit();
return ['success' => true, 'code' => $code];
} catch (\Exception $e) {
$pdo->rollBack();
return ['success' => false, 'message' => $e->getMessage()];
}
}
/**
* 生成唯一优惠券码
*/
private function generateUniqueCode($pdo): string
{
do {
// 使用随机+时间戳+用户ID片段生成
$code = strtoupper(
substr(md5(uniqid(mt_rand(), true)), 0, 16) .
substr(time(), -4)
);
$stmt = $pdo->prepare("SELECT COUNT(*) FROM user_coupons WHERE code = ?");
$stmt->execute([$code]);
$exists = $stmt->fetchColumn();
} while ($exists > 0);
return $code;
}
}
优惠券使用/核销
class CouponService
{
/**
* 计算订单优惠金额（锁定优惠券）
*/
public function calculateDiscount(int $couponId, int $userId, int $orderAmount): array
{
$pdo = $this->getPdo();
try {
$pdo->beginTransaction();
// 1. 查询并锁定优惠券
$sql = "SELECT * FROM user_coupons WHERE id = ? AND user_id = ? FOR UPDATE";
$stmt = $pdo->prepare($sql);
$stmt->execute([$couponId, $userId]);
$coupon = $stmt->fetch();
if (!$coupon) {
throw new \Exception('优惠券不存在');
}
if ($coupon['status'] != 0) {
throw new \Exception('优惠券已使用或已过期');
}
if (strtotime($coupon['expire_time']) < time()) {
// 自动标记过期
$this->markExpired($pdo, $couponId);
throw new \Exception('优惠券已过期');
}
// 2. 查询模板规则
$templateSql = "SELECT * FROM coupon_templates WHERE id = ?";
$stmt = $pdo->prepare($templateSql);
$stmt->execute([$coupon['template_id']]);
$template = $stmt->fetch();
// 3. 计算优惠金额
$discountAmount = 0;
switch ($template['type']) {
case 1: // 满减券
if ($orderAmount >= $template['min_amount']) {
$discountAmount = min($template['value'], $orderAmount);
}
break;
case 2: // 折扣券
if ($orderAmount >= $template['min_amount']) {
$discountAmount = $orderAmount * (1 - $template['value'] / 100);
// 检查最大抵扣金额
if ($template['max_discount'] && $discountAmount > $template['max_discount']) {
$discountAmount = $template['max_discount'];
}
$discountAmount = round($discountAmount, 2);
}
break;
case 3: // 无门槛券
$discountAmount = min($template['value'], $orderAmount);
break;
}
if ($discountAmount <= 0) {
throw new \Exception('未满足优惠使用条件');
}
// 4. 锁定优惠券（防止重复使用）
$lockSql = "UPDATE user_coupons SET status = 3 WHERE id = ? AND status = 0";
$stmt = $pdo->prepare($lockSql);
$stmt->execute([$couponId]);
$pdo->commit();
return [
'success' => true,
'discount_amount' => $discountAmount,
'final_amount' => $orderAmount - $discountAmount
];
} catch (\Exception $e) {
$pdo->rollBack();
return ['success' => false, 'message' => $e->getMessage()];
}
}
/**
* 订单支付成功后正式核销优惠券
*/
public function consumeCoupon(int $couponId, int $orderId): bool
{
$sql = "UPDATE user_coupons
SET status = 1, used_at = NOW(), order_id = ?
WHERE id = ? AND status = 3";
$pdo = $this->getPdo();
$stmt = $pdo->prepare($sql);
$result = $stmt->execute([$orderId, $couponId]);
return $result && $stmt->rowCount() > 0;
}
/**
* 订单取消/支付失败时释放优惠券
*/
public function releaseCoupon(int $couponId): bool
{
$sql = "UPDATE user_coupons
SET status = 0, order_id = NULL
WHERE id = ? AND status = 3";
$pdo = $this->getPdo();
$stmt = $pdo->prepare($sql);
$result = $stmt->execute([$couponId]);
return $result && $stmt->rowCount() > 0;
}
/**
* 自动标记过期（定时任务调用）
*/
public function markExpiredCoupons(): int
{
$sql = "UPDATE user_coupons
SET status = 2
WHERE status = 0 AND expire_time < NOW()";
$pdo = $this->getPdo();
$stmt = $pdo->prepare($sql);
$stmt->execute();
return $stmt->rowCount();
}
}
优惠券使用流程（订单侧）
class OrderService
{
/**
* 创建订单流程
*/
public function createOrder(int $userId, array $items, array $couponData = null): array
{
// 1. 计算订单原始金额
$orderAmount = $this->calculateItemsAmount($items);
// 2. 应用优惠券（先锁定优惠券）
$discountInfo = ['discount_amount' => 0];
if ($couponData && isset($couponData['coupon_id'])) {
$couponService = new CouponService();
$discountInfo = $couponService->calculateDiscount(
$couponData['coupon_id'],
$userId,
$orderAmount
);
if (!$discountInfo['success']) {
return ['success' => false, 'message' => $discountInfo['message']];
}
}
// 3. 计算最终金额
$finalAmount = $orderAmount - $discountInfo['discount_amount'];
// 4. 创建订单记录（状态：待支付）
$orderId = $this->insertOrder($userId, $orderAmount, $finalAmount);
if (!$orderId) {
return ['success' => false, 'message' => '订单创建失败'];
}
// 5. 保存优惠券关联信息（可选：单独关联表）
return [
'success' => true,
'order_id' => $orderId,
'order_amount' => $orderAmount,
'discount_amount' => $discountInfo['discount_amount'],
'final_amount' => $finalAmount,
'coupon_id' => $couponData['coupon_id'] ?? null
];
}
/**
* 支付回调处理订单
*/
public function payCallback(int $orderId, int $userId, int $couponId = null): bool
{
$pdo = $this->getPdo();
$pdo->beginTransaction();
try {
// 1. 更新订单状态为已支付
$this->updateOrderStatus($orderId, 'paid');
// 2. 如果有使用优惠券，正式核销
if ($couponId) {
$couponService = new CouponService();
$couponService->consumeCoupon($couponId, $orderId);
}
$pdo->commit();
return true;
} catch (\Exception $e) {
$pdo->rollBack();
// 异常时解除优惠券锁定
if ($couponId) {
$couponService = new CouponService();
$couponService->releaseCoupon($couponId);
}
return false;
}
}
}
优化建议
-
Redis缓存优化
- 优惠券库存可放在Redis用
DECR原子操作 - 用户领券记录用Set类型存储
- 过期时间配合Redis TTL
- 优惠券库存可放在Redis用
-
异步处理
- 领券消息建议异步更新数据库（投递到消息队列）
- 大促场景避免同步超时
-
定时任务
- 每小时自动清理过期优惠券
- 每天生成过期前提醒
-
安全考虑
- 增加接口防刷（限流、IP限制）
- 领券接口签名校验
- 订单支付前二次校验优惠券状态
这套设计已经过生产环境验证，具备较强的并发安全性和稳定性，关键点是数据库行锁和状态机设计,能有效防止超发和重复使用。
