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

'pdo_code' => 'HY000',
'db_code' => 1396,
'db_error' => 'Operation CREATE USER failed for 'user'@'host''

// 安全创建用户的函数
function createMySQLUserSafe($pdo, $username, $password, $host = 'localhost') {
  try {
    // 首先检查用户是否已存在
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM mysql.user WHERE User = ? AND Host = ?");
    $stmt->execute([$username, $host]);
    $userExists = $stmt->fetchColumn() > 0;

    if ($userExists) {
      // 用户已存在，先删除
      $dropStmt = $pdo->prepare("DROP USER ?@?");
      $dropStmt->execute([$username, $host]);
    }

    // 创建新用户
    $createStmt = $pdo->prepare("CREATE USER ?@? IDENTIFIED BY ?");
    $createStmt->execute([$username, $host, $password]);

    // 授予基本权限
    $grantStmt = $pdo->prepare("GRANT USAGE ON *.* TO ?@?");
    $grantStmt->execute([$username, $host]);

    return true;
  } catch (PDOException $e) {
    // 处理特定错误码
    if ($e->getCode() == '1396') {
      // 记录错误信息
      error_log("用户创建失败: " . $e->getMessage());
      return false;
    } else {
      // 重新抛出其他异常
      throw $e;
    }
  }
}

# 查看所有用户
SELECT User, Host FROM mysql.user;

# 检查特定用户是否存在
SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'hostname';

# 查看当前用户权限
SHOW GRANTS FOR CURRENT_USER;

# 修复权限表（谨慎使用）
mysql_upgrade --force