MySQL 8.0.40
Source Code Documentation
Stored Programs

In MySQL 5.0 the protocol was extended to handle:

Multi-Resultset

Multi-resultsets are sent by a stored program if more than one resultset was generated inside of it. e.g.:

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;
enum_sp_type::PROCEDURE
@ PROCEDURE
BEGIN
#define BEGIN
Definition: lexyy.cc:128
consts::SELECT
const std::string SELECT("SELECT")
Name of the static privileges.
sha2_password::DELIMITER
const char DELIMITER
Definition: i_sha2_password.h:66
IF
@ IF
Definition: sql_yacc.h:275
EXISTS
@ EXISTS
Definition: sql_yacc.h:216
CREATE
@ CREATE
Definition: sql_yacc.h:144
TEMPORARY
@ TEMPORARY
Definition: sql_yacc.h:613
DROP
@ DROP
Definition: sql_yacc.h:189
VALUES
@ VALUES
Definition: sql_yacc.h:664
INTO
@ INTO
Definition: sql_yacc.h:291
TABLE
Definition: table.h:1399

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 0x0A
  • another 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 03 fe 00 00 0a 00 with its status-flag being 0x0A
  • 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 ise 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 the processing of 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 flags.

OUT Parameter Set

Starting with MySQL 5.5.3, prepared statements can bind OUT parameters of stored procedures. They are returned as an extra resultset in the multi-resultset response. The client announces it can handle OUT parameters by setting the CLIENT_PS_MULTI_RESULTS capability.

To distinguish a normal resultset from an OUT parameter set, the EOF_Packet or (if CLIENT_DEPRECATE_EOF capability flag is set) OK_Packet that follows its field definition has the SERVER_PS_OUT_PARAMS flag set.

Note
The closing EOF_Packet does NOT have either SERVER_PS_OUT_PARAMS flag nor the SERVER_MORE_RESULTS_EXISTS flag set. Only the first EOF_Packet has.

Multi-Statement

A multi-statement is permitting COM_QUERY to send more than one query to the server, separated by ; characters.

The client musst announce that it wants multi-statements by either setting the CLIENT_MULTI_STATEMENTS capability or by using COM_SET_OPTION