具有可变大小变量列表的 MySQL 准备语句 [英] MySQL Prepared statements with a variable size variable list
问题描述
你将如何用 PHP 编写一个准备好的 MySQL 语句,每次使用不同数量的参数?此类查询的示例是:
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:
SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)
IN
子句每次运行时都会有不同数量的 id
.
The IN
clause will have a different number of id
s each time it is run.
我有两种可能的解决方案,但想看看是否有更好的方法.
I have two possible solutions in my mind but want to see if there is a better way.
可能的解决方案 1 使语句接受 100 个变量,并用保证不在表中的虚拟值填充其余变量;多次调用超过 100 个值.
Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.
可能的解决方案 2 不要使用准备好的语句;构建并运行查询检查可能的注入攻击.
Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.
推荐答案
我能想到几个解决方案.
I can think of a couple solutions.
一种解决方案可能是创建一个临时表.将 in 子句中的每个参数插入表中.然后对您的临时表进行简单的连接.
One solution might be to create a temporary table. Do an insert into the table for each parameter that you would have in the in clause. Then do a simple join against your temporary table.
另一种方法可能是做这样的事情.
Another method might be to do something like this.
$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$parmcount=count($parms); // = 4
$inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,?
$sql='SELECT age, name FROM people WHERE id IN (%s)';
$preparesql=sprintf($sql,$inclause); // = example statement used in the question
$st=$dbh->prepare($preparesql);
$st->execute($parms);
我怀疑但没有证据表明第一个解决方案可能更适用于较大的列表,而后者适用于较小的列表.
I suspect, but have no proof, that the first solution might be better for larger lists, and the later would work for smaller lists.
为了让@orrd 开心,这里有一个简洁的版本.
To make @orrd happy here is a terse version.
$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$st=$dbh->prepare(sprintf('SELECT age, name FROM people WHERE id IN (%s)',
implode(',',array_fill(0,count($parms),'?'))));
$st->execute($parms);
这篇关于具有可变大小变量列表的 MySQL 准备语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!