php 带有命名占位符的 INSERT 和 ON DUPLICATE KEY UPDATE 的 PDO 准备语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14089055/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO prepared statements for INSERT and ON DUPLICATE KEY UPDATE with named placeholders
提问by Chaya Cooper
I'd like to switch PDO INSERT and UPDATE prepared statements to INSERT and ON DUPLICATE KEY UPDATE since I think it'll be a lot more efficient than what I'm currently doing, but I'm having trouble figuring out the correct syntax to use with named placeholders and bindParam.
我想将 PDO INSERT 和 UPDATE 准备好的语句切换为 INSERT 和 ON DUPLICATE KEY UPDATE 因为我认为它会比我目前正在做的更有效率，但我无法找出正确的语法与命名占位符和 bindParam 一起使用。
I found several similar question on SO, but I'm new to PDO and couldn't successfully adapt the code for my criteria. This is what I've tried, but it doesn't work (it doesn't insert or update):
我在 SO 上发现了几个类似的问题，但我是 PDO 的新手，无法成功地根据我的标准调整代码。这是我尝试过的，但它不起作用（它不插入或更新）：
try {
$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)'
'ON DUPLICATE KEY UPDATE customer_info SET fname= :fname,
lname= :lname
WHERE user_id = :user_id');
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);
$stmt->execute();
}
This is a simplified version of my code (I have several queries, and each query has between 20 - 50 fields). I'm currently updating first and checking if the number of rows updated is greater than 0 and if not then running the Insert, and each of those queries has it's own set of bindParam statements.
这是我的代码的简化版本（我有几个查询，每个查询有 20 - 50 个字段）。我目前正在首先更新并检查更新的行数是否大于 0，如果不是，则运行插入，并且每个查询都有自己的一组 bindParam 语句。
回答by Barmar
Your ON DUPLICATE KEYsyntax is not correct.
您的ON DUPLICATE KEY语法不正确。
$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);
You don't need to put the table name or SETin the ON DUPLICATE KEYclause, and you don't need a WHEREclause (it always updates the record with the duplicate key).
你不需要把表名或SET将在ON DUPLICATE KEY条款，而你并不需要一个WHERE条款（它总是更新具有重复键的记录）。
See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
见http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
You also had a PHP syntax error: you split the query up into two strings.
您还遇到了 PHP 语法错误：您将查询拆分为两个字符串。
UPDATE:
更新：
To bind multiple parameters:
绑定多个参数：
function bindMultiple($stmt, $params, &$variable, $type) {
foreach ($params as $param) {
$stmt->bindParam($param, $variable, $type);
}
}
Then call it:
然后调用它：
bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);
回答by Ligemer
IMHO below is the right answer for anyone coming across this again.
Note: this statement assumes user_id is a KEY in the table.
恕我直言，下面是再次遇到此问题的任何人的正确答案。
注意：此语句假定 user_id 是表中的一个 KEY。
The STATEMENT indeed was wrong, but the accepted answer was not completely correct.
STATEMENT 确实是错误的，但接受的答案并不完全正确。
If you're inserting and updating using the same values (and not updating with different values), this is the query pseudo-code corrected:
如果您使用相同的值插入和更新（而不是使用不同的值更新），这是更正的查询伪代码：
try {
//optional if your DB driver supports transactions
$conn->beginTransaction();
$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) ' .
'VALUES(:user_id, :fname, :lname)' .
'ON DUPLICATE KEY UPDATE fname=VALUES(fname), lname=VALUES(lname)');
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);
$stmt->execute();
//again optional if on MyIASM or DB that doesn't support transactions
$conn->commit();
} catch (PDOException $e) {
//optional as above:
$conn->rollback();
//handle your exception here $e->getMessage() or something
}
