Documentation Home
MySQL Internals Manual
Download this Manual
EPUB - 1.2Mb


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
Sign Up Login You must be logged in to post a comment.