如何使用mysql实现验证码校验_mysql验证码存储设计

验证码表需包含phone_or_email、code、expires_at、used、created_at字段，设phone_or_email为唯一键，建(phone_or_email,used)联合索引和expires_at单索引；插入用ON DUPLICATE KEY覆盖旧码；校验用原子UPDATE语句；定时清理过期记录。

验证码存储表设计

验证码通常需要短期有效、单次使用、绑定用户行为（如手机号或邮箱），所以数据库表要包含关键字段：唯一标识（如手机号）、验证码值、过期时间、是否已使用、创建时间。推荐用以下结构：

• phone_or_email：VARCHAR(50)，作为查询主键，支持手机号或邮箱
• code：CHAR(6) 或 VARCHAR(10)，存纯数字验证码（如“123456”）
• expires_at：DATETIME，设为当前时间 + 5 分钟（例如 NOW() + INTERVAL 5 MINUTE）
• used：TINYINT(1)，默认 0（未使用），校验成功后更新为 1
• created_at：DATETIME DEFAULT CURRENT_TIMESTAMP

建议给 (phone_or_email, used) 加联合索引，方便快速查未使用的最新码；同时对 expires_at 单独建索引，便于定时清理过期记录。

插入验证码（发送时）

用户点击“获取验证码”后，生成随机 6 位数字（PHP 可用 random_int(100000, 999999)），然后执行插入语句，覆盖旧的未使用记录：

INSERT INTO verify_codes (phone_or_email, code, expires_at, used) 
VALUES ('138****1234', '654321', NOW() + INTERVAL 5 MINUTE, 0)
ON DUPLICATE KEY UPDATE 
  code = VALUES(code), 
  expires_at = VALUES(expires_at), 
  used = 0;

前提是 phone_or_email 设为唯一键（UNIQUE KEY）。这样能防止重复发码堆积，也避免用户频繁刷码。

校验验证码（登录/注册时）

用户提交验证码后，需原子性地检查三项：是否存在、是否过期、是否未使用。推荐一条 SQL 完成验证并标记为已用：

UPDATE verify_codes 
SET used = 1 
WHERE phone_or_email = '138****1234' 
  AND code = '654321' 
  AND expires_at > NOW() 
  AND used = 0;

执行后检查 ROW_COUNT() 是否为 1。是 → 校验通过；否 → 提示“验证码错误、已过期或已被使用”。不建议先 SELECT 再 UPDATE，避免并发时被重复使用。

自动清理过期数据

长期运行中，过期但未使用的记录会累积。可每天用事件（EVENT）清理：

CREATE EVENT clean_expired_codes
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM verify_codes WHERE expires_at < NOW() AND used = 0;

也可在应用层定时任务中执行相同 SQL。注意：不要删 used = 1 的记录，便于审计或排查问题（保留 7 天即可）。