MySQLi预处理语句中的绑定参数
问题描述：
sample1:
<?php
$mysqli = new mysqli("localhost","root","","test");
/*check connection*/
if(mysqli_connect_errno())
{
printf("connection failed: %s
",mysqli_connect_error());
exit();
}
/*create prapared statement*/
$stmt1 = $mysqli->prepare("select id from posts");
$stmt2 =$mysqli->prepare("select username from members where id=?");
test($stmt1,$stmt2);//function call
function test($stmt1,$stmt2)
{
$stmt1->execute();
$stmt1->store_result();
$stmt1->bind_result($ID);
while($stmt1->fetch())
{
**/*bind params*/
$stmt2->bind_param('i',$id); /*HERE,BINDING MANY TIMES*/**
/*set params*/
$id =$ID;
/*execute prapared statement*/
$stmt2->execute();
/*bind results*/
$stmt2->bind_result($username);
while($stmt2->fetch())
{
echo 'Username: '.$username.'<br/>';
}
}
}
?>
sample2:
<?php
$mysqli = new mysqli("localhost","root","","test");
/*check connection*/
if(mysqli_connect_errno())
{
printf("connection failed: %s
",mysqli_connect_error());
exit();
}
/*create prapared statement*/
$stmt1 = $mysqli->prepare("select id from posts");
$stmt2 =$mysqli->prepare("select username from members where id=?");
test($stmt1,$stmt2);//function call
function test($stmt1,$stmt2)
{
$stmt1->execute();
$stmt1->store_result();
$stmt1->bind_result($ID);
**/*bind params*/
$stmt2->bind_param('i',$id); /*HERE,BINDING ONCE*/**
while($stmt1->fetch())
{
/*set params*/
$id =$ID;
/*execute prapared statement*/
$stmt2->execute();
/*bind results*/
$stmt2->bind_result($username);
while($stmt2->fetch())
{
echo 'Username: '.$username.'<br/>';
}
}
}
?>
Is there any performance difference in both the ways how parameters are bound (shown in bold in sample1 and sample2) or MySQLi automatically handles this? In sample1,although it is unnecessary bind_param is included inside the while loop.
答
Calling a function repeatedly is more expensive than not calling it repeatedly, so there should be a small performance gain in the second version. There's no point in calling bind_param inside the loop since it's unnecessary -- the first call binds to a reference to the variable, so all you have to do is reassign the variable.