为符合条件的客户分配唯一代金券的数据库方案咨询

针对你遇到的问题，核心矛盾是Vouchers表缺乏唯一约束、交易表截断导致Row ID失效，以及需要严格控制客户参与资格。以下是落地的解决步骤：

1. 补全Vouchers表的唯一标识（最关键一步） #

原Vouchers表没有主键/唯一键是根本问题，必须先解决：

• 若已有现成代金券码字段，直接给该字段加唯一约束：

  ALTER TABLE Vouchers ADD CONSTRAINT uc_voucher_code UNIQUE (voucher_code);
  

• 若无现成代金券码，生成3000个唯一字符串（比如UUID、自定义编码VOUCH-0001到VOUCH-3000）作为voucher_code插入表，同时新增is_used字段标记使用状态：

  ALTER TABLE Vouchers ADD COLUMN is_used TINYINT(1) DEFAULT 0;
  

2. 新建关联表，锁死参与资格和代金券唯一性 #

创建customer_voucher_assignment表，专门记录客户与代金券的绑定关系，从根源避免重复：

CREATE TABLE customer_voucher_assignment (
    phone_number VARCHAR(20) NOT NULL,
    voucher_code VARCHAR(50) NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (phone_number), -- 强制每个手机号只能参与一次
    FOREIGN KEY (voucher_code) REFERENCES Vouchers(voucher_code),
    UNIQUE KEY (voucher_code) -- 强制每张代金券只能分配一次
);

这个表的核心作用：

• phone_number作为主键，直接阻断同一客户重复参与的可能
• voucher_code加唯一约束，杜绝一张券分给多个人的情况
• assigned_at记录分配时间，天然符合先到先得的顺序

3. 充值触发分配的原子性逻辑 #

当客户完成10美元及以上充值时，用事务执行以下操作（保证原子性，避免并发问题）：

START TRANSACTION;

-- 检查客户是否已领过券
SELECT 1 FROM customer_voucher_assignment WHERE phone_number = '客户手机号';
IF FOUND_ROWS() = 0 THEN
    -- 锁定一张未使用的代金券，防止并发抢券
    SELECT voucher_code INTO @selected_voucher FROM Vouchers 
    WHERE is_used = 0 
    ORDER BY voucher_code LIMIT 1 
    FOR UPDATE;

    -- 若还有可用券，完成绑定并标记为已使用
    IF @selected_voucher IS NOT NULL THEN
        INSERT INTO customer_voucher_assignment (phone_number, voucher_code) 
        VALUES ('客户手机号', @selected_voucher);
        
        UPDATE Vouchers SET is_used = 1 WHERE voucher_code = @selected_voucher;
    END IF;
END IF;

COMMIT;

• FOR UPDATE行锁能有效避免高并发下多个请求同时抢到同一张券
• 事务确保绑定和标记使用的操作要么全部成功，要么全部回滚，不会出现数据不一致

4. 绕过交易表截断的问题 #

交易表每5分钟截断的问题完全不影响这个方案：

• 客户是否有参与资格，完全由customer_voucher_assignment表判断，和交易表无关
• 之前的备份表可保留充值记录，但核心的资格校验不需要依赖交易表的Row ID

额外优化建议 #

• 插入代金券时直接控制总数量为3000，避免超发
• 高并发场景下，可将代金券分配逻辑封装成存储过程，减少应用层交互
• 定期检查customer_voucher_assignment表的记录数，当达到3000时，直接关闭充值领券入口

内容的提问来源于stack exchange，提问作者srikanth