問題描述

我想知道準備好的語句是否與具有多個 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();

?>

這篇關于PHP MySQLi 多次插入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！