PHP MySQLi 多次插入

2021-12-25 00:00:00 php mysqli

我想知道准备好的语句是否与具有多个 VALUES 的普通 mysql_query 工作相同.

I'm wondering if prepared statements work the same as a normal mysql_query with multiple VALUES.

INSERT INTO table (a,b) VALUES ('a','b'), ('c','d');

VS

$sql = $db->prepare('INSERT INTO table (a,b) VALUES (?, ?);

如果我在循环中使用准备好的语句，MySQL 是在后台优化插入以使其像在那里的第一段代码中一样工作，还是就像在循环中运行第一段代码一样每次值?

If I use the prepared statement in a loop, is MySQL optimizing the insert in the background to work like it would in the first piece of code there, or is it just like running the first piece of code inside a loop with one value each time ?

推荐答案

我继续进行了一个测试，其中一个查询使用准备好的语句，另一个构建整个查询然后执行该查询.我可能没有让我想知道的内容易于理解.

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

这是我的测试代码.我在想准备好的语句会阻止执行，直到调用 $stmt->close() 来优化它或其他东西.但情况似乎并非如此，因为使用 real_escape_string 构建查询的测试至少要快 10 倍.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

相关文章