在PDO中，ON DUPLICATE KEY UPDATE无法正常工作 [英] ON DUPLICATE KEY UPDATE not working in PDO

问题描述

INSERT INTO b (id, website...) 
VALUES (:id, :website...)
ON DUPLICATE KEY UPDATE  
website=:website ...

我有一个MySQL查询，我的SET ID是唯一的，为什么

I have a MYSQL QUERY, I have SET id unique, why

website=:website ...

不起作用，当我更改为website="whatever"时它起作用.有人知道为什么吗?

is not working, when I change to website="whatever" it works. anyone know why?

$job_B->bindValue(':website', $website, PDO::PARAM_STR);

推荐答案

在准备好的语句中，您遇到了PDO命名参数的不幸和误导性行为.尽管分配了名称，但实际上您不能多次使用参数，如 c1>文档:

You have run into an unfortunate and misleading behavior of PDO's named parameters in a prepared statement. Despite assigning names, you cannot actually use a parameter more than once, as mentioned in the prepare() documentation:

调用PDOStatement :: execute()时，对于要传递给语句的每个值，必须包含一个唯一的参数标记. 您不能在准备好的语句中两次使用相同名称的命名参数标记.您不能在例如SQL语句的IN()子句中将多个值绑定到单个命名参数.

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 twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

这意味着您需要将参数绑定两次，使用两个不同的名称，并因此使用两个不同的bindValue()调用:

This means you'll need to bind the parameter twice, with two different names, and consequently two different bindValue() calls:

$stmt = $pdo->prepare("
  INSERT INTO b (id, website...) 
  VALUES (:id, :website_insert...)
  ON DUPLICATE KEY UPDATE  
    website=:website_update ...
");

// Later, bind for each
$job_B->bindValue(':id', ...);
// Same value twice...
$job_B->bindValue(':website_insert', $website, PDO::PARAM_STR);
$job_B->bindValue(':website_update', $website, PDO::PARAM_STR);

这篇关于在PDO中，ON DUPLICATE KEY UPDATE无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！