mysqli::multi_query
mysqli_multi_query
(PHP 5, PHP 7, PHP 8)
mysqli::multi_query -- mysqli_multi_query — データベース上でひとつ以上のクエリを実行する
説明
オブジェクト指向型
手続き型
セミコロンで連結されたひとつまたは複数のクエリを実行します。
セキュリティ上の注意: SQLインジェクション
クエリに入力値を含める場合は、プリペアドステートメント を使うべきです。使わない場合、データを適切にフォーマットし、全ての文字列は mysqli_real_escape_string() を使ってエスケープしなければいけません。
ひとつの呼び出し中に、クエリはデータベースに非同期に送信されますが、 データベースはそれらを順番に実行します。 mysqli_multi_query() は PHP に制御を戻す前に、最初のクエリの実行が完了するのを待ちます。 MySQLサーバーはその後、次のクエリを順番に実行していきます。 次の結果セットの準備が出来ると、 MySQL は PHP 側が mysqli_next_result() を実行するのを待ちます。
複数のクエリを処理するために、 do-while ループ を使うことを推奨します。 全てのクエリの実行が完了し、結果を PHP が取得するまで、 接続はビジー状態になります。 次のクエリを順番に実行するには、 mysqli_next_result() を使います。 次の結果セットの準備ができていない場合、 mysqli は MySQLサーバーからの応答を待ちます。 更に結果セットがあるかどうかを調べるには mysqli_more_results() を使います。
SELECT, SHOW, DESCRIBE
や
EXPLAIN
のように
結果セットを生成するクエリについては、
結果セットを取得するために
mysqli_use_result() や
mysqli_store_result() が使えます。
結果セットを生成しないクエリについては、
影響した行数のような情報を取得するために、
同じ関数が使えます。
ストアドプロシージャを実行するために
CALL
を実行すると、
複数の結果セットが生じる場合があります。
ストアドプロシージャに
SELECT
が含まれている場合、
結果セットは実行されるプロシージャが生成する順番で返されます。
一般的には、呼び出し側はどの程度の量、
結果セットが返されるかはわからないので、
複数の結果を取得する準備をしておかなければいけません。
プロシージャの最終的な実行結果は、結果ステータスです。
結果ステータスには、結果セットが存在しないことも含みます。
この結果ステータスは、プロシージャが成功したか、
エラーが発生したかを示します。
パラメータ
link
手続き型のみ: mysqli_connect() あるいは mysqli_init() が返す mysqliオブジェクト。
query
-
実行されるクエリを含む文字列。 複数のクエリの場合、セミコロンで区切らなければいけません。
戻り値
最初のステートメントが失敗した場合にのみ false
を返します。
その他のステートメントのエラーを取得するには、まず
mysqli_next_result() をコールする必要があります。
エラー / 例外
mysqli のエラー報告 (MYSQLI_REPORT_ERROR
) が有効になっており、かつ要求された操作が失敗した場合は、警告が発生します。さらに、エラー報告のモードが MYSQLI_REPORT_STRICT
に設定されていた場合は、mysqli_sql_exception が代わりにスローされます。
例
例1 mysqli::multi_query() の例
オブジェクト指向型
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* 複数のクエリを実行します */
$mysqli->multi_query($query);
do {
/* PHP 側に結果セットを保存します */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
}
/* 区切り線を出力します */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
手続き型
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* 複数のクエリを実行します */
mysqli_multi_query($link, $query);
do {
/* PHP 側に結果セットを保存します */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
}
/* 区切り線を出力します */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
上の例の出力は、 たとえば以下のようになります。
my_user@localhost ----------------- Amersfoort Maastricht Dordrecht Leiden Haarlemmermeer
参考
- mysqli_query() - データベース上でクエリを実行する
- mysqli_use_result() - 結果セットの取得を開始する
- mysqli_store_result() - 直近のクエリから結果セットを転送する
- mysqli_next_result() - multi_query の、次の結果を準備する
- mysqli_more_results() - マルチクエリからの結果がまだ残っているかどうかを調べる
User Contributed Notes 22 notes
WATCH OUT: if you mix $mysqli->multi_query and $mysqli->query, the latter(s) won't be executed!
<?php
// BAD CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
$mysqli->query(" SQL statement #1 ; ") // not executed!
$mysqli->query(" SQL statement #2 ; ") // not executed!
$mysqli->query(" SQL statement #3 ; ") // not executed!
$mysqli->query(" SQL statement #4 ; ") // not executed!
?>
The only way to do this correctly is:
<?php
// WORKING CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
?>
To be able to execute a $mysqli->query() after a $mysqli->multi_query() for MySQL > 5.3, I updated the code of jcn50 by this one :
<?php
// WORKING CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) // flush multi_queries
{
if (!$mysqli->more_results()) break;
}
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
?>
Here are more details about error checking and return values from multi_query(). Testing shows that there are some mysqli properties to check for each result:
affected_rows
errno
error
insert_id
warning_count
If error or errno are not empty then the remaining queries did not return anything, even though error and errno will appear to be empty if processing further results is continued.
Also note that get_warnings() will not work with multi_query(). It can only be used after looping through all results, and it will only get the warnings for the last one of the queries and not for any others. If you need to see or log query warning strings then you must not use multi_query(), because you can only see the warning_count value.
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.
I was developing my own CMS and I was having problem with attaching the database' sql file. I thought mysqli_multi_query got bugs where it crashes my MySQL server. I tried to report the bug but it showed that it has duplicate bug reports of other developers. To my surprise, mysqli_multi_query needs to bother with result even if there's none.
I finally got it working when I copied the sample and removed somethings. Here is what it looked liked
<?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 = "CREATE TABLE....;...;... blah blah blah;...";
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
//do nothing since there's nothing to handle
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
//I just kept this since it seems useful
//try removing and see for yourself
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>
bottom-line: I think mysql_multi_query should only be used for attaching a database. it's hard to handle results from 'SELECT' statements inside a single while loop.
Following code can be used to resolve
mysqli::next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method
$query = "SELECT SOME_COLUMN FROM SOME_TABLE_NAME;";
$query .= "SELECT SOME_OTHER_COLUMN FROM SOME_TABLE_NAME";
/* execute multi query */
if (mysqli_multi_query($this->conn, $query)) {
$i = true;
do {
/* store first result set */
if ($result = mysqli_store_result($this->conn)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($this->conn)) {
$i = true;
printf("-----------------\n");
} else {
$i = false;
}
} while ($i && mysqli_next_result($this->conn));
}
You can use prepared statements on stored procedures.
You just need to flush all the subsequent result sets before closing the statement... so:
$mysqli_stmt = $mysqli->prepare(....);
... bind, execute, bind, fetch ...
while($mysqli->more_results())
{
$mysqli->next_result();
$discard = $mysqli->store_result();
}
$mysqli_stmt->close();
Hope that helps :o)
Use generator.
PHP 5.5.0
<?php
// Quick multiQuery func.
function multiQuery( mysqli $mysqli, $query ) {
if ($mysqli->multi_query( $query )) {
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
foreach ($row as $key => $value) yield $key => $value;
}
$result->free();
}
} while( $mysqli->more_results() && $mysqli->next_result() );
}
}
$query = "OPTIMIZE TABLE `question`;" .
"LOCK TABLES `question` READ;" .
"SELECT * FROM `question` WHERE `questionid`=2;" .
"SELECT * FROM `question` WHERE `questionid`=7;" .
"SELECT * FROM `question` WHERE `questionid`=8;" .
"SELECT * FROM `question` WHERE `questionid`=9;" .
"SELECT * FROM `question` WHERE `questionid`=11;" .
"SELECT * FROM `question` WHERE `questionid`=12;" .
"UNLOCK TABLES;" .
"TRUNCATE TABLE `question`;";
$mysqli = new mysqli('localhost', 'user', 'pswd', 'dbnm');
$mysqli->set_charset("cp1251");
// result
foreach ( multiQuery($mysqli, $query) as $key => $value ) {
echo $key, $value, PHP_EOL;
}
?>
Good luck!
I appreciate the advice from crmccar at gmail dot com regarding the proper way to check for errors, but I would get an error with his/her code. I fixed it by changing the code a little:
<?php
$sql = file_get_contents( 'sql/test_' . $id . '_data.sql');
$query_array = explode(';', $sql);
// Run the SQL
$i = 0;
if( $this->mysqli->multi_query( $sql ) )
{
do {
$this->mysqli->next_result();
$i++;
}
while( $this->mysqli->more_results() );
}
if( $this->mysqli->errno )
{
die(
'<h1>ERROR</h1>
Query #' . ( $i + 1 ) . ' of <b>test_' . $id . '_data.sql</b>:<br /><br />
<pre>' . $query_array[ $i ] . '</pre><br /><br />
<span style="color:red;">' . $this->mysqli->error . '</span>'
);
}
?>
I'd like to reinforce the correct way of catching errors from the queries executed by multi_query(), since the manual's examples don't show it and it's easy to lose UPDATEs, INSERTs, etc. without knowing it.
$mysqli->next_result() will return false if it runs out of statements OR if the next statement has an error. Therefore, it's important to check for errors when the loop ends. Also, I believe it's useful to know when and where the loop broke, so consider the following code:
<?php
$statements = array("INSERT INTO tablename VALUES ('1', 'one')", "INSERT INTO tablename VALUES ('2', 'two')");
if ($mysqli->multi_query(implode(';', $statements))) {
$i = 0;
do {
$i++;
} while ($mysqli->next_result());
}
if ($mysqli->errno) {
echo "Batch execution prematurely ended on statement $i.\n";
var_dump($statements[$i], $mysqli->error);
}
?>
The IF statement on the multi_query() call checks the first result, because next_result() starts at the second.
If you want to create a table with triggers, procedures or functions in one multiline query you may stuck with a error -
#1064 - You have an error in your SQL syntax; xxx corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
The solution is very simple - don't use DELIMITER keyword at all! So, instead of :
DELIMITER |
CREATE TRIGGER $dbName.$iname BEFORE INSERT ON $table FOR EACH ROW
BEGIN
<body>
EOT|
DELIMITER ;
just use :
CREATE TRIGGER $dbName.$iname BEFORE INSERT ON $table FOR EACH ROW
BEGIN
<body>
EOT;
For more information read answers at StackOverflow for question #5311141
http://stackoverflow.com/questions/5311141/how-to-execute-mysql-command-delimiter
If your second or late query returns no result or even if your query is not a valid SQL query, more_results(); returns true in any case.
Be sure to not send a set of queries that are larger than max_allowed_packet size on your MySQL server. If you do, you'll get an error like:
Mysql Error (1153): Got a packet bigger than 'max_allowed_packet' bytes
To see your MySQL size limitation, run the following query: show variables like 'max_allowed_packet';
or see http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
To get the affected/selected row count from all queries
$q = "UPDATE `Review` SET `order` = 1 WHERE id = 600;" // aff 1
. "UPDATE `Review` SET `order` = 600 WHERE id = 1;" //aff 1
. "SELECT 0;" //for testing, aff rows == -1
;
$affcnt = 0;
$rowcnt = 0;
$res = $db->multi_query($q);
if($res == false)
Lib::throw( $q . "\n[" . $db->errno . "]\n" . $db->error . "\n" );
do
{
$affcnt += $db->affected_rows;
if( isset($res->num_rows) )
$rowcnt += $res->num_rows;
}
while( $db->more_results() && $res = $db->next_result() );
//IMPORTANT: call more_results First!, THEN next_result to get new data.
return $res;
Getting "Error: Commands out of sync; you can't run this command now" after running a multi-query? Make sure you've cleared out the queue of results.
Here's what I've used to discard all subsequent results from a multi-query:
<?php
while($dbLink->more_results() && $dbLink->next_result()) {
$extraResult = $dbLink->use_result();
if($extraResult instanceof mysqli_result){
$extraResult->free();
}
}
?>