Documentation Home
MySQL Internals Manual


14.8.1 Multi-Resultset

Multi-resultsets are sent up by stored procedures if more than one resultset was generated inside of it:

CREATE TEMPORARY TABLE ins ( id INT );
DROP PROCEDURE IF EXISTS multi;
DELIMITER $$
CREATE PROCEDURE multi() BEGIN
  SELECT 1;
  SELECT 1;
  INSERT INTO ins VALUES (1);
  INSERT INTO ins VALUES (2);
END$$
DELIMITER ;

CALL multi();
DROP TABLE ins;

results in:

  • a resultset:

    01 00 00 01 01 17 00 00    02 03 64 65 66 00 00 00    ..........def...
    01 31 00 0c 3f 00 01 00    00 00 08 81 00 00 00 00    .1..?...........
    05 00 00 03 fe 00 00 0a    00 02 00 00 04 01 31 05    ..............1.
    00 00 05 fe 00 00 0a 00                               ........
    • see the EOF_Packet: 05 00 00 03 fe 00 00 0a 00 with its status-flag being 0a

  • the 2nd resultset:

    01 00 00 06 01 17 00 00    07 03 64 65 66 00 00 00    ..........def...
    01 31 00 0c 3f 00 01 00    00 00 08 81 00 00 00 00    .1..?...........
    05 00 00 08 fe 00 00 0a    00 02 00 00 09 01 31 05    ..............1.
    00 00 0a fe 00 00 0a 00                               ........
    • see the EOF_Packet: 05 00 00 0a fe 00 00 0a 00 with its status-flag being 0a

  • ... and a closing empty resultset, an OK_Packet:

    07 00 00 0b 00 01 00 02    00 00 00                   ...........

If the SERVER_MORE_RESULTS_EXISTS flag is set, that indicates more resultsets will follow.

The trailing OK_Packet is the response to the CALL statement and contains the affected-rows count of the last statement. In our case we inserted 2 rows, but only the affected_rows of the last INSERT statement is returned as part of the OK_Packet. If the last statement is a SELECT, the affected-rows count is 0.

As of MySQL 5.7.5, the resultset is followed by an OK_Packet, and this OK_Packet has the SERVER_MORE_RESULTS_EXISTS flag set to start processing the next resultset.

The client has to announce that it wants multi-resultsets by either setting the CLIENT_MULTI_RESULTS or CLIENT_PS_MULTI_RESULTS capability.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.