php - 如何修复 SQLSTATE[42000] 错误；使用准备好的语句

Question

我正在尝试创建电子邮件确认脚本。

这是我的 PHP 代码:

...
$q = $dbh->prepare("INSERT INTO `email_confirm` (UserID,token,tokenDate) VALUES(:id, :token, UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE token = VALUES(:token), tokenDate = UTC_TIMESTAMP();");
$result = $q -> execute( array( ":id" => $this->id, ":token" => $token ) );
...

运行时，我收到以下错误:

 Caught exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?), tokenDate = UTC_TIMESTAMP()' at line 1

我不是 MySQL 方面的专家，但我在我的代码中找不到任何语法错误，我希望得到一些帮助。

Answer 1

根据 PDO::prepare 记录:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

虽然您可以添加一个 :token2 占位符或类似的占位符，但恰好绑定(bind)到相同的值，实际上是 MySQL 的 VALUES() ON DUPLICATE KEY UPDATE 子句中的函数采用列名而不是文字。因此，这将起到作用:

$q = $dbh->prepare('
  INSERT INTO email_confirm
    (UserID, token, tokenDate)
  VALUES
    (:id, :token, UTC_TIMESTAMP())
  ON DUPLICATE KEY UPDATE
    token = VALUES(token),
    tokenDate = UTC_TIMESTAMP()
');

但是，您可能需要查看 Automatic Initialization and Updating for TIMESTAMP and DATETIME ，而不是尝试重新实现轮子。