PHP mysqli_multi_query(): mehrere Queries in einem Aufruf

Ein Installationsskript soll zwei Tabellen anlegen und ein paar Startwerte einfügen. Mit mysqli_query() und allen Anweisungen in einem String meldet MySQL nur einen Syntaxfehler. Mit PHP mysqli_multi_query() klappt das Einfügen, aber die nächste Abfrage danach scheitert mit einer Meldung, die niemand erwartet hat. Welche Schnittstelle zu MySQL passt, vergleicht das Tutorial PDO und mysqli im Vergleich, dort steht multi_query als Spezialfähigkeit von mysqli in der Tabelle. Hier geht es darum, wie man die Funktion benutzt und welche Fallen ein Stapel aus mehreren Anweisungen hat.

Alle Beispiele liefen mit PHP 8.4 unter Windows und Linux gegen MySQL 8.0, mit denselben Ausgaben.

Was PHP mysqli_multi_query() macht

Die Funktion schickt mehrere SQL-Anweisungen, getrennt durch ein Semikolon, in einem Aufruf an den Server. MySQL arbeitet sie nacheinander ab und legt für jede ein Ergebnis bereit. PHP bekommt die Kontrolle zurück, sobald die erste Anweisung fertig ist, den Rest erledigt der Server laut Handbuch parallel dazu. Bis jedes Ergebnis abgeholt ist, bleibt die Verbindung belegt. Der Weg durch einen Stapel sieht so aus:

flowchart TD
    A[multi_query] --> B{erste Anweisung ok?}
    B -- nein --> C[Exception oder false sofort]
    B -- ja --> D[Ergebnis abholen]
    D --> E{more_results?}
    E -- nein --> F[Verbindung wieder frei]
    E -- ja --> G[next_result schaltet weiter]
    G --> H{Anweisung ok?}
    H -- ja --> D
    H -- nein --> I[Exception, Rest läuft nicht]

Syntax und Rückgabewert im Original

Die prozedurale Form nimmt die Verbindung als ersten Parameter, die objektorientierte nur den SQL-Text:

mysqli_multi_query(

    mysqli $mysql,

    string $query

): bool



/* object style: $mysqli->multi_query($query)



   query  - one or multiple SQL statements,

            separated by a semicolon

   return - false if the first query failed,

            true otherwise; errors of the other

            queries are returned by

            mysqli_next_result()



   The MySQL server executes the statements one

   after another and asynchronously from PHP.

   The connection stays busy until every result

   has been returned to PHP: loop with

   mysqli_more_results() and mysqli_next_result().

   Statements in a multi query cannot be

   prepared, so the database gets no parameters,

   only strings. */

Der Rückgabewert von PHP mysqli_multi_query() verrät also nur etwas über die erste Anweisung. Ob die dritte gescheitert ist, zeigt erst der Aufruf von next_result(), der zu ihr weiterschaltet. Seit PHP 8.1 wirft mysqli in der Grundeinstellung Exceptions, gemessen stand der Meldemodus ohne eigene Einstellung auf 3, also MYSQLI_REPORT_ERROR plus MYSQLI_REPORT_STRICT.

Die Schleife mit store_result(), more_results() und next_result()

Jede Anweisung im Stapel liefert ein Ergebnis, auch ein CREATE oder UPDATE. Die Schleife holt es mit store_result() ab, prüft mit more_results(), ob noch eines folgt, und schaltet mit next_result() weiter. $db ist eine offene mysqli-Verbindung:

<?php

$sql = "CREATE TABLE kunde (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(40) NOT NULL UNIQUE

);

INSERT INTO kunde (name) VALUES ('Anna'), ('Ben');

UPDATE kunde SET name = 'Benno' WHERE name = 'Ben';

SELECT id, name FROM kunde ORDER BY id;

SELECT COUNT(*) AS anzahl FROM kunde";



$db->multi_query($sql);

$nr = 0;

do {

    $nr++;

    $res = $db->store_result();

    if ($res instanceof mysqli_result) {

        echo "#$nr: {$res->num_rows} Zeilen\n";

        $res->free();

    } else {

        echo "#$nr: {$db->affected_rows} betroffen\n";

    }

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

/* #1: 0 betroffen

   #2: 2 betroffen

   #3: 1 betroffen

   #4: 2 Zeilen

   #5: 1 Zeilen */

Für CREATE, INSERT und UPDATE liefert store_result() false, ohne dass ein Fehler vorliegt, field_count steht dann auf 0. Dann stehen affected_rows und insert_id für genau diese Anweisung bereit. Beim INSERT mit zwei Zeilen war insert_id die ID der ersten Zeile, also 1.

Commands out of sync: wenn die Schleife fehlt

Lässt ein Skript nach PHP mysqli_multi_query() die Schleife weg und schickt gleich die nächste Abfrage, antwortet die Verbindung mit einer Exception:

<?php

$db->multi_query('SELECT 1; SELECT 2');

try {

    $db->query('SELECT 3');

} catch (mysqli_sql_exception $e) {

    echo $e->getCode(), ': ', $e->getMessage();

}

/* 2014: Commands out of sync; you can't run

   this command now */

Das erste Ergebnis abzuholen, genügte im Test nicht, ohne next_result() kam dieselbe Meldung. Tückischer ist die Kurzform, die in vielen Forenantworten steht: while ($db->more_results() && $db->next_result()) {}. Sie ruft next_result() auf, ohne die Ergebnisse abzuholen. Bei einem Stapel aus INSERT-Anweisungen reichte das, weil dort keine Ergebnismenge wartet. Bei SELECT 1; SELECT 2 lieferte schon der erste Aufruf von next_result() still false, ohne Fehlernummer und ohne Exception. Die Schleife endete beim ersten Ergebnis, und die nächste Abfrage scheiterte wieder mit 2014, auch nachdem dieses Ergebnis noch abgeholt und freigegeben war. Sicher ist nur eine Schleife, die jedes Ergebnis abholt und freigibt:

<?php

function stapel_leeren(mysqli $db): void

{

    do {

        $r = $db->store_result();

        if ($r instanceof mysqli_result) {

            $r->free();

        }

        $weiter = $db->more_results();

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

}



$db->multi_query('SELECT 1; SELECT 2');

stapel_leeren($db);

echo $db->query('SELECT 3')->fetch_row()[0];

/* 3 */

Ohne Exceptions, also mit MYSQLI_REPORT_OFF, liefert die zweite Abfrage stattdessen false, und $db->errno steht auf 2014. Dieselbe Meldung entsteht auch bei einer ungepufferten Abfrage, die nicht zu Ende gelesen wurde, diesen Fall behandelt das Tutorial mysqli_free_result().

Fehler im dritten Statement: was läuft und was nicht

Im folgenden Stapel ist in der dritten Anweisung der Spaltenname vertippt, die Tabelle kunde ist vorher leer. Der Zähler $nr merkt sich, bei welcher Anweisung die Schleife gerade ist:

<?php

$sql = "INSERT INTO kunde (name) VALUES ('A1');

INSERT INTO kunde (name) VALUES ('A2');

INSERT INTO kunde (nmae) VALUES ('A3');

INSERT INTO kunde (name) VALUES ('A4')";



$nr = 1;

try {

    $db->multi_query($sql);

    while ($db->more_results()) {

        $nr++;

        $db->next_result();

    }

} catch (mysqli_sql_exception $e) {

    echo "Anweisung $nr: ", $e->getMessage(), "\n";

}

/* Anweisung 3: Unknown column 'nmae'

   in 'field list' */



$r = $db->query(

    'SELECT GROUP_CONCAT(name) FROM kunde'

);

echo $r->fetch_row()[0];

/* A1,A2 */

PHP mysqli_multi_query() selbst lieferte true, die Exception kam erst aus next_result(). Die vierte Anweisung lief nicht mehr, MySQL bricht den Stapel beim ersten Fehler ab. Der try-Block muss deshalb die ganze Schleife umfassen. Ohne Exceptions liefert next_result() an derselben Stelle false, errno steht auf 1054 und more_results() auf false. Ein doppelter Schlüssel in der zweiten von drei Anweisungen verhielt sich genauso, Fehler 1062, nur die erste Zeile stand in der Tabelle.

Scheitert schon die erste Anweisung, etwa an einem Tippfehler wie SELEKT, wirft PHP mysqli_multi_query() sofort: You have an error in your SQL syntax mit Code 1064. Die Verbindung ist danach gleich wieder nutzbar. Fängt niemand die Exception, endet das Skript mit Fatal error: Uncaught mysqli_sql_exception. Im Stack Trace steht dann mysqli->multi_query() als Ursprung, bei einem Fehler weiter hinten im Stapel mysqli->next_result(). Wie man Fehlernummern im Code auswertet und welche im Alltag oft vorkommen, zeigt das Tutorial zu mysqli_errno().

Transaktionen: der Stapel rollt nicht von selbst zurück

Naheliegend wäre, den Stapel mit START TRANSACTION zu beginnen und mit COMMIT zu beenden. Scheitert eine Anweisung dazwischen, wird das COMMIT aber nie erreicht. Im Test blieb die Transaktion offen: Die eigene Verbindung sah die erste Zeile, eine zweite gar nichts, und erst ein rollback() räumte auf. Jede weitere Anweisung auf dieser Verbindung wäre in der offenen Transaktion gelandet. Sicher ist die Transaktion außerhalb des Stapels, hier wieder mit leerer Tabelle und dem Stapel von oben:

<?php

$db->begin_transaction();

try {

    $db->multi_query($sql);

    while ($db->more_results()) {

        $db->next_result();

    }

    $db->commit();

} catch (mysqli_sql_exception $e) {

    $db->rollback();

    echo 'Zurückgerollt: ', $e->getCode(), "\n";

}

/* Zurückgerollt: 1054 */



$r = $db->query('SELECT COUNT(*) FROM kunde');

echo $r->fetch_row()[0];

/* 0 */

Die Tabelle blieb leer. Eine Ausnahme kennt MySQL trotzdem: Steht zwischen den INSERTs ein CREATE TABLE, schreibt die Datenbank die Transaktion an dieser Stelle fest. Die erste Zeile war nach dem rollback() noch da.

Kein Prepared Statement und das Risiko SQL-Injection

Was PHP mysqli_multi_query() ausführt, lässt sich nicht vorbereiten. prepare('SELECT 1; SELECT 2') scheiterte ebenso wie query() mit derselben Zeichenkette an Fehler 1064 mit near 'SELECT 2'. Werte stehen in einem Stapel also immer direkt im SQL-Text. Was daraus folgt, zeigt ein Angriffswert an einer Wegwerftabelle:

<?php

/* stammt sonst aus $_GET['id'] */

$id = '1; DROP TABLE notiz';

$sql = "SELECT text FROM notiz WHERE id = $id";



try {

    $db->query($sql);

} catch (mysqli_sql_exception $e) {

    echo $e->getCode(), "\n";

}

/* 1064, die Tabelle bleibt */



$db->multi_query($sql);

stapel_leeren($db);

$r = $db->query("SHOW TABLES LIKE 'notiz'");

echo $r->num_rows;

/* 0, die Tabelle ist gelöscht */

query() nimmt nur eine Anweisung an, dieser Angriff endet dort als Syntaxfehler. Sicher ist query() mit einem eingesetzten Wert trotzdem nicht: 1 OR 1=1 las dort ohne Fehlermeldung alle Zeilen der Tabelle, es gehen lediglich keine zweiten Anweisungen durch. PHP mysqli_multi_query() führt dagegen beide aus. real_escape_string() hilft hier nicht: Auf diesen Wert angewendet, gab die Funktion ihn unverändert zurück, weil er kein Anführungszeichen enthält. Im Stapel haben Werte von außen deshalb nichts verloren, und auch sonst gehören sie in Prepared Statements. Wie Prepared Statements die Eingaben sicher vom SQL trennen, zeigt das Tutorial SQL-Injection verhindern.

Schneller als mysqli_query() in der Schleife? Gemessen

Eine häufige Frage lautet, ob PHP mysqli_multi_query() dasselbe ist wie mehrere Aufrufe von mysqli_query(), nur schneller. Gemessen wurden 1000 INSERTs in eine InnoDB-Tabelle auf sieben Wegen:

Ohne Transaktion kosten Stapel und Schleife fast gleich viel, weil MySQL jede Anweisung einzeln festschreibt. Die rund 11 ms je Commit gehen auf das Konto des Docker-Datenträgers, auf einem echten Server ist der Wert kleiner, die Reihenfolge bleibt. Der Stapel spart Netzwege, das zeigt sich erst in einer Transaktion. Gleichauf mit dem INSERT mit vielen Zeilen in VALUES und dabei sicher war ein Prepared INSERT mit vielen Platzhaltern:

<?php

$namen = ['Anna', 'Ben', 'Carla'];

$platz = implode(

    ',',

    array_fill(0, count($namen), '(?)')

);

$stmt = $db->prepare(

    "INSERT INTO kunde (name) VALUES $platz"

);

$stmt->execute($namen);

echo $stmt->affected_rows;

/* 3 */

Wie viele Anweisungen in einen Stapel passen, begrenzt kein Zähler, sondern die Paketgröße max_allowed_packet. Auf dem Testserver stand sie auf 64 MiB, ein Stapel aus 10.000 INSERTs mit knapp 500 KB lief ohne Fehler.

SQL-Dateien, DELIMITER und Prozeduren

Ein typischer Einsatz von PHP mysqli_multi_query() ist der Import einer eigenen SQL-Datei bei der Installation. Das Skript liest die Datei ein und arbeitet sie mit der Funktion von oben ab:

<?php

$sql = file_get_contents(__DIR__ . '/schema.sql');

$db->multi_query($sql);

stapel_leeren($db);

echo "Import fertig\n";

Ein Dump aus der Kommandozeile enthält oft DELIMITER // vor einer gespeicherten Prozedur. Das ist ein Befehl des Kommandozeilen-Clients, den der Server nicht kennt, der Import brach mit Fehler 1064 ab. Ohne DELIMITER funktionierte CREATE PROCEDURE mit einem BEGIN-Block im Stapel dagegen problemlos, der Server erkennt das Ende des Blocks selbst. Ein CALL lieferte für zwei SELECTs in der Prozedur drei Ergebnisse: die beiden Ergebnismengen und eine zusätzliche Statusmeldung ohne Daten. Wer die nicht abholt, bekommt wieder 2014.

Ein Semikolon am Ende schadet nicht, ein doppeltes in der Mitte wie SELECT 1;;SELECT 2 ergab nach dem ersten Ergebnis Fehler 1064. Kommentare mit --, # und /* */ waren kein Problem.

Wann PHP mysqli_multi_query() passt und was stattdessen

Die Funktion ist ein Werkzeug für feste Skripte, deren SQL vollständig im Code oder in einer eigenen Datei steht: Installation, Migration, nächtliche Wartung, Import des eigenen Schemas. Sobald ein einziger Wert von außen kommt, gehört er in ein Prepared Statement. Für viele Zeilen ist ein INSERT mit vielen Platzhaltern schneller als jeder Stapel. Einzelaufrufe in einer Transaktion kosten mehr Netzwege, haben aber keine der Fallen von oben. Für Werte aus Formularen gibt es keinen Grund, PHP mysqli_multi_query() zu verwenden.

Fazit

PHP mysqli_multi_query() schickt mehrere Anweisungen in einem Aufruf und meldet dabei nur über die erste etwas. Jede weitere Antwort holt die Schleife aus store_result(), more_results() und next_result() ab, und zwar vollständig, sonst blockiert die Verbindung mit „Commands out of sync“. Spätere Fehler fliegen beim Weiterschalten, der Rest des Stapels läuft dann nicht mehr, und eine Transaktion im Stapel bleibt offen.

Weil sich bei PHP mysqli_multi_query() nichts binden lässt, kommen nur feste SQL-Texte in Frage. Für Massen-INSERTs ist ein Prepared Statement mit vielen Platzhaltern schneller und sicher, für Installations- und Wartungsskripte bleibt der Stapel ein bequemer Weg.