MySQL错误码 1022 分析与解决方案

'pdo_code' => '23000',
'db_code' => 1022,
'db_error' => 'Can\'t write; duplicate key in table'

// 方法1: 使用 INSERT IGNORE
$sql = "INSERT IGNORE INTO users (username, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$username, $email]);
$affectedRows = $stmt->rowCount();

// 方法2: 使用 ON DUPLICATE KEY UPDATE
$sql = "INSERT INTO users (username, email, last_login) "
        . "VALUES (?, ?, NOW()) "
        . "ON DUPLICATE KEY UPDATE last_login = NOW()";
$stmt = $pdo->prepare($sql);
$stmt->execute([$username, $email]);

// 方法3: 先检查后操作
$checkSql = "SELECT COUNT(*) FROM users WHERE username = ?";
$checkStmt = $pdo->prepare($checkSql);
$checkStmt->execute([$username]);
$exists = $checkStmt->fetchColumn() > 0;

if ($exists) {
  // 更新现有记录
  $updateSql = "UPDATE users SET email = ?, last_login = NOW() WHERE username = ?";
  $updateStmt = $pdo->prepare($updateSql);
  $updateStmt->execute([$email, $username]);
} else {
  // 插入新记录
  $insertSql = "INSERT INTO users (username, email) VALUES (?, ?)";
  $insertStmt = $pdo->prepare($insertSql);
  $insertStmt->execute([$username, $email]);
}

# 查看表的索引和约束信息
SHOW INDEX FROM table_name;

# 查看表结构
DESCRIBE table_name;

# 查看创建表的SQL语句
SHOW CREATE TABLE table_name;