php PDO 的查询与执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4700623/
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's query vs execute
提问by Qiao
Are they both do the same thing, only differently?
他们都做同样的事情，只是不同吗？
Is there any difference besides using preparebetween
除了使用prepare之间还有什么区别
$sth = $db->query("SELECT * FROM table");
$result = $sth->fetchAll();
and
和
$sth = $db->prepare("SELECT * FROM table");
$sth->execute();
$result = $sth->fetchAll();
?
?
回答by Gilean
queryruns a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues.
query运行标准 SQL 语句并要求您正确转义所有数据以避免 SQL 注入和其他问题。
executeruns a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters. executewill also perform better if you are repeating a query multiple times. Example of prepared statements:
execute运行一个准备好的语句，它允许您绑定参数以避免需要转义或引用参数。execute如果您多次重复查询，也会表现得更好。准备好的语句示例：
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories);
$sth->bindParam(':colour', $colour);
$sth->execute();
// $calories or $color do not need to be escaped or quoted since the
// data is separated from the query
Best practice is to stick with prepared statements and executefor increased security.
最佳做法是坚持使用准备好的语句并execute提高安全性。
See also: Are PDO prepared statements sufficient to prevent SQL injection?
回答by netcoder
No, they're not the same. Aside from the escaping on the client-side that it provides, a prepared statement is compiled on the server-side once, and then can be passed different parametersat each execution. Which means you can do:
不，它们不一样。除了它提供的客户端转义外，准备好的语句在服务器端编译一次，然后可以在每次执行时传递不同的参数。这意味着您可以：
$sth = $db->prepare("SELECT * FROM table WHERE foo = ?");
$sth->execute(array(1));
$results = $sth->fetchAll(PDO::FETCH_ASSOC);
$sth->execute(array(2));
$results = $sth->fetchAll(PDO::FETCH_ASSOC);
They generally will give you a performance improvement, although not noticeable on a small scale. Read more on prepared statements (MySQL version).
它们通常会给您带来性能改进，尽管在小范围内并不明显。阅读有关准备好的语句（MySQL 版本）的更多信息。
回答by Jeff Puckett
Gilean's answeris great, but I just wanted to add that sometimes there are rare exceptions to best practices, and you might want to test your environment both ways to see what will work best.
Gilean 的回答很好，但我只想补充一点，有时最佳实践有极少数例外，您可能希望以两种方式测试您的环境，看看哪种方式最有效。
In one case, I found that queryworked faster for my purposes because I was bulk transferring trusted data from an Ubuntu Linux box running PHP7 with the poorly supported Microsoft ODBC driver for MS SQL Server.
在一种情况下，我发现这query对我的目的来说工作得更快，因为我从运行 PHP7 的 Ubuntu Linux 机器批量传输受信任的数据，并且对 MS SQL Server 的 Microsoft ODBC 驱动程序支持不佳。
I arrived at this question because I had a long running script for an ETLthat I was trying to squeeze for speed. It seemed intuitive to me that querycould be faster than prepare& executebecause it was calling only one function instead of two. The parameter binding operation provides excellent protection, but it might be expensive and possibly avoided if unnecessary.
我之所以遇到这个问题，是因为我有一个长时间运行的ETL脚本，我试图压缩它以提高速度。对我来说似乎很直观，它query可能比prepare&更快，execute因为它只调用一个函数而不是两个函数。参数绑定操作提供了极好的保护，但它可能很昂贵，并且在不必要时可能会避免。
Given a couple rare conditions:
鉴于几个罕见的条件：
If you can't reuse a prepared statement because it's not supported by the Microsoft ODBC driver.
If you're not worried about sanitizing input and simple escaping is acceptable. This may be the case because binding certain datatypes isn't supported by the Microsoft ODBC driver.
PDO::lastInsertIdis not supported by the Microsoft ODBC driver.
如果由于Microsoft ODBC 驱动程序不支持而无法重用准备好的语句。
如果您不担心清理输入，那么简单的转义是可以接受的。这可能是因为Microsoft ODBC 驱动程序不支持绑定某些数据类型。
PDO::lastInsertIdMicrosoft ODBC 驱动程序不支持。
Here's a method I used to test my environment, and hopefully you can replicate it or something better in yours:
这是我用来测试我的环境的方法，希望您可以复制它或在您的环境中使用更好的方法：
To start, I've created a basic table in Microsoft SQL Server
首先，我在 Microsoft SQL Server 中创建了一个基本表
CREATE TABLE performancetest (
sid INT IDENTITY PRIMARY KEY,
id INT,
val VARCHAR(100)
);
And now a basic timed test for performance metrics.
现在是性能指标的基本定时测试。
$logs = [];
$test = function (String $type, Int $count = 3000) use ($pdo, &$logs) {
$start = microtime(true);
$i = 0;
while ($i < $count) {
$sql = "INSERT INTO performancetest (id, val) OUTPUT INSERTED.sid VALUES ($i,'value $i')";
if ($type === 'query') {
$smt = $pdo->query($sql);
} else {
$smt = $pdo->prepare($sql);
$smt ->execute();
}
$sid = $smt->fetch(PDO::FETCH_ASSOC)['sid'];
$i++;
}
$total = (microtime(true) - $start);
$logs[$type] []= $total;
echo "$total $type\n";
};
$trials = 15;
$i = 0;
while ($i < $trials) {
if (random_int(0,1) === 0) {
$test('query');
} else {
$test('prepare');
}
$i++;
}
foreach ($logs as $type => $log) {
$total = 0;
foreach ($log as $record) {
$total += $record;
}
$count = count($log);
echo "($count) $type Average: ".$total/$count.PHP_EOL;
}
I've played with multiple different trial and counts in my specific environment, and consistently get between 20-30% faster results with querythan prepare/execute
我在我的特定环境中进行了多次不同的试验和计数，并且始终query比prepare/快 20-30% 的结果execute
5.8128969669342 prepare
5.8688418865204 prepare
4.2948560714722 query
4.9533629417419 query
5.9051351547241 prepare
4.332102060318 query
5.9672858715057 prepare
5.0667371749878 query
3.8260300159454 query
4.0791549682617 query
4.3775160312653 query
3.6910600662231 query
5.2708210945129 prepare
6.2671611309052 prepare
7.3791449069977 prepare
(7) prepare Average: 6.0673267160143
(8) query Average: 4.3276024162769
5.8128969669342制备
5.8688418865204制备
4.2948560714722查询
4.9533629417419查询
5.9051351547241制备
4.332102060318查询
5.9672858715057制备
5.0667371749878查询
3.8260300159454查询
4.0791549682617查询
4.3775160312653查询
3.6910600662231查询
5.2708210945129制备
6.2671611309052制备
7.3791449069977制备
（7）准备平均：6.0673267160143
（8）查询平均：4.3276024162769
I'm curious to see how this test compares in other environments, like MySQL.
我很想知道这个测试在其他环境中的比较，比如 MySQL。
