mysqli_multi

Description

Procedural style:

bool mysqli_multi_query ( mysqli link, string query )

Object oriented style (method):

class mysqli {

bool multi_query ( string query )

}

The mysqli_multi_query() executes one or multiple queries which are concatenated by a semicolon.

To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().

范例

例子 1. Object oriented style


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

例子 2. Procedural style


<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>

上例将输出：

my_user@localhost
-----------------
Amersfoort
Maastricht
Dordrecht
Leiden
Haarlemmermeer

add a note User Contributed Notes

info at ff dot net
08-May-2006 07:02


Note that you need to use this function to call Stored Procedures!



If you experience "lost connection to MySQL server" errors with your Stored Procedure calls then you did not fetch the 'OK' (or 'ERR') message, which is a second result-set from a Stored Procedure call. You have to fetch that result to have no problems with subsequent queries.



Bad example, will FAIL now and then on subsequent calls:

<?php

$sQuery='CALL exampleSP('param')';

if(!mysqli_multi_query($this->sqlLink,$sQuery))

  $this->queryError();

$this->sqlResult=mysqli_store_result($this->sqlLink);

?>



Working example:

<?php

$sQuery='CALL exampleSP('param')';

if(!mysqli_multi_query($this->sqlLink,$sQuery))

  $this->queryError();

$this->sqlResult=mysqli_store_result($this->sqlLink);



if(mysqli_more_results($this->sqlLink))

  while(mysqli_next_result($this->sqlLink));

?>



Of course you can do more with the multiple results then just throwing them away, but for most this will suffice. You could for example make an "sp" function which will kill the 2nd 'ok' result.



This nasty 'OK'-message made me spend hours trying to figure out why MySQL server was logging warnings with 'bad packets from client' and PHP mysql_error() with 'Connection lost'. It's a shame the mysqli library does catch this by just doing it for you.

Nick
31-Jan-2006 01:34


I have to amend my previous post, I was misusing microtime().  multi_query is faster even for 2 queries on an internal network.



For the record, microtime(TRUE) is what I should have used instead of microtime().

Nick
28-Jan-2006 12:11


I assume this is here for performance, to avoid having to establish a communication link over the network repeatedly.  My tests reveal some serious overhead though. 



Communicating on an internal network at 100Mbps I had to combine over 300 queries to see a speed increase, and under that it was faster to do them individually.  



I wouldn't recommend using this to combine 3 or 4 queries if all you're after is speed. It may be more worthwhile if your database calls have to go out over the internet.



Here's the benchmark I used:

<?php

$reps = 100;

$db = new mysqli("yourserver.com", "username", "password", "dbname");

$query = "SELECT column FROM table WHERE id=1";



$start = microtime();

for ($i = 0; $i < $reps; $i++) {

    $result = $db->query($query);

    $result->close;

}

$end = microtime();

echo ($end - $start) . "<br>\n";



$start = microtime();

for ($i = 0; $i < $reps; $i++) {

    $myquery .= $query . "; ";

}

$db->multi_query($myquery);

do {

    $result = $db->store_result();

    $result->close;

} while ($db->next_result());

$end = microtime();

echo ($end - $start) . "<br>\n";

?>

add a note

mysqli_multi_query

Description

返回值

参见

范例