在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屋!