mysql - REPLACE INTO SQL 语法错误

Question

我有 REPLACE 的 SQL 语法

  $query = "REPLACE INTO ".TBL_ACTIVE_ADMINS." VALUES (:username, :time, :userlevel)";
  $stmt = $this->connection->prepare($query);
  $stmt->execute(array(':username' => $username, ':time' => $time, ':userlevel' => $userlevel));

这工作正常，但我想稍微调整一下，以便其中有一个 where 子句只允许 :userlevel 为“9”我使用 REPLACE SELECT 尝试了此操作，但我不断收到错误

  $query = "REPLACE INTO ".TBL_ACTIVE_ADMINS."(:username, :time, :userlevel) SELECT * FROM ".TBL_USERS." WHERE userlevel = 9 SET username = :username and timestamp = :time and userlevel = :userlevel";
  $stmt = $this->connection->prepare($query);
  $stmt->execute(array(':username' => $username, ':time' => $time, ':userlevel' => $userlevel));

谁能向我解释一下我在上面的代码中做错了什么？

Answer 1

没关系；在对代码进行了很多修改之后，我决定从不同的角度来看待它。我现在已经编写了一个函数，可以在每次刷新页面时清理 SQL 表，从而准确地统计出当前在我的网站上活跃的用户。

我使用了下面的代码，然后在调用计数之前我清理了数据库表

$query = "REPLACE INTO ".TBL_ACTIVE_ADMINS." VALUES (:username, :time, :userlevel)";
$stmt = $this->connection->prepare($query);
$stmt->execute(array(':username' => $username, ':time' => $time, ':userlevel' => $userlevel));

所以我的最终代码看起来像这样

$query = "REPLACE INTO ".TBL_ACTIVE_ADMINS." VALUES (:username, :time, :userlevel)";
$stmt = $this->connection->prepare($query);
$stmt->execute(array(':username' => $username, ':time' => $time, ':userlevel' => $userlevel));

$query = "DELETE FROM ".TBL_ACTIVE_ADMINS." WHERE userlevel = 3";
$stmt = $this->connection->prepare($query);
$stmt->execute(array(':userlevel' => $userlevel));

$this->calcNumActiveAdmins();