1062 php data_error ai_generated true

PDOException：SQLSTATE[23000]：完整性约束违反：键 'users_email_unique' 的重复条目 '[email protected]'

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '[email protected]' for key 'users_email_unique'

ID: php/pdo-sqlstate-23000-duplicate-entry-unique-constraint

其他格式: JSON · Markdown 中文 · English
85%修复率
90%置信度
1证据数
2024-02-10首次发现

版本兼容性

版本状态引入弃用备注
MySQL 8.0.34 active — — —
MariaDB 10.11.0 active — — —
PHP 8.2.0 active — — —

根因分析

INSERT 或 UPDATE 操作尝试在具有 UNIQUE 约束的列中创建重复值，通常由于竞态条件、缺少检查或并发请求导致。

English

An INSERT or UPDATE operation attempted to create a duplicate value in a column that has a UNIQUE constraint, usually due to race conditions, missing checks, or concurrent requests.

generic

官方文档

https://dev.mysql.com/doc/refman/8.0/en/error-mysql-server.html#error_er_dup_entry

解决方案

  1. 使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 优雅处理重复：
$stmt = $pdo->prepare('INSERT INTO users (email, name) VALUES (:email, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)');
$stmt->execute([':email' => '[email protected]', ':name' => 'John']);
  2. 实现带有短暂延迟的重试机制：
try { $stmt->execute(); } catch (PDOException $e) { if ($e->errorInfo[1] == 1062) { usleep(100000); $stmt->execute(); } else { throw $e; } }

无效尝试

常见但无效的做法:

  1. Removing the UNIQUE constraint on the column to allow duplicates 95% 失败

    This violates data integrity and can lead to application logic bugs; not a real fix but a workaround that breaks the schema.

  2. Wrapping every INSERT in a SELECT to check existence first without using transactions 80% 失败

    Race conditions still occur between SELECT and INSERT in concurrent environments; does not solve the problem reliably.