問題描述

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

如果我在循環(huán)中使用準(zhǔn)備好的語句，MySQL 是在后臺優(yōu)化插入以使其像在那里的第一段代碼中一樣工作，還是就像在循環(huán)中運(yùn)行第一段代碼一樣每次值?

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 ?

推薦答案

我繼續(xù)進(jìn)行了一個測試，其中一個查詢使用準(zhǔn)備好的語句，另一個構(gòu)建整個查詢?nèi)缓髨?zhí)行該查詢.我可能沒有讓我想知道的內(nèi)容易于理解.

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.

這是我的測試代碼.我在想準(zhǔn)備好的語句會阻止執(zhí)行，直到調(diào)用 $stmt->close() 來優(yōu)化它或其他東西.但情況似乎并非如此，因?yàn)槭褂?real_escape_string 構(gòu)建查詢的測試至少要快 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();

?>

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