php - PDO 与绑定(bind) IN 运算符相关的问题

Question

这个问题已经有答案了:

Can I bind an array to an IN() condition in a PDO query? (23 个回答)

已关闭 7 年前。

Note: I see this question as a probable serious issue in PDO Drivers. I can pretty much understand the difference between an array and string. So, please consider testing this on your sandbox before Deleting or making duplicate.

$pdo = db()->getInstance();

$sql = "SELECT * FROM clients WHERE client_id IN :clients";
$params = [ ":clients" =>  "(223,224,225)" ];    

$stmt = $pdo->prepare($sql);
try{
    $stmt->execute($params);
} catch( \Exception $e){
    die("Query Execution Failed" . $e->getMessage());
}

Query Execution FailedSQLSTATE[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 ''(223,224,225)'' at line 1

这里的问题不是转化为:

SELECT * FROM clients WHERE client_id IN (223,224,225)

它被翻译为:

 SELECT * FROM clients WHERE client_id IN '(223,224,225)'

这里没有数组。我只是提供一个参数clients来替换:clients 。 为什么要添加 quotes周围？

Answer 1

首先，我(在某种程度上)同意你的观点。这是一个问题，但不是很严重的问题。也许他们故意保留它。或者，也许你的愚蠢问题有一些辩论潜力。

Every PDO parameter gets wrapped with quotes. (Personally I think should not be). When you pass the IN string at once, it puts quote around it which fails the query. But, you can do that (put quotes) with individual item. So, pass parameter to each item instead of preparing string before.

现在，解决方案:

$pdo = db()->getInstance();

$sql = "SELECT * FROM clients WHERE client_id IN :clients";

$clients = [223,224,225];

/* You could use bind with ? here. I am just making this with named parameters so that you could echo SQL */

$params = [];
$replacement = [];

foreach ($clients as $key => $value){
    $replacement[] = ":client$key";
    $params[":client" . $key] = $value;
}
$sql = str_replace(":clients", "("  . implode(",",$replacement) . ")", $sql);

echo $sql;
/* SELECT * FROM clients WHERE client_id IN (:client0,:client1,:client2) */

$stmt = $pdo->prepare($sql);
try{
    $stmt->execute($params);
    print_pre($stmt->fetchAll(PDO::FETCH_ASSOC));
} catch( \Exception $e){
    die("Query Execution Failed" . $e->getMessage());
}

这就像一个魅力。我尝试创建一个虚拟客户表。但问题是你的实际 SQL 现在变成了:

SELECT * FROM clients WHERE client_id IN ('223','224','225')

大多数人可能不会在意它，但你可能会损失一些性能，因为查询将 id 转换为带引号的字符串，特别是你有一个大型数据库、复杂的查询并且更喜欢整数。