MySQL :: MySQL Internals Manual :: 15.7 Prepared Statements

MySQL Internals Manual :: 15 MySQL Client/Server Protocol :: 15.7 Prepared Statements

Section Navigation [Toggle]

15.7. Prepared Statements

15.7.1. Binary Protocol Resultset
15.7.2. Binary Protocol Resultset Row
15.7.3. Binary Protocol Value
15.7.4. COM_STMT_PREPARE
15.7.5. COM_STMT_SEND_LONG_DATA
15.7.6. COM_STMT_EXECUTE
15.7.7. COM_STMT_CLOSE
15.7.8. COM_STMT_RESET

The prepared statement protocol was introduced in MySQL 4.1 and adds a few new commands:

It also defines a more compact resultset format that is used instead of the ProtocolText::Resultset to return resultsets.

Keep in mind that not all statements can be prepared:

http://forge.mysql.com/worklog/task.php?id=2871

15.7.1. Binary Protocol Resultset

Binary Protocol Resultset is similar to the ProtocolText::Resultset. It just contains the rows in Binary Protocol Resultset Row format.

ProtocolBinary::Resultset:

lenenc_int column_count > 0
column_count * Protocol::ColumnDefinition
EOF_Packet
none or many ProtocolBinary::ResultsetRow
EOF_Packet

Example

01 00 00 01 01|1a 00 00    02 03 64 65 66 00 00 00    ..........def...
04 63 6f 6c 31 00 0c 08    00 06 00 00 00 fd 00 00    .col1...........
1f 00 00|05 00 00 03 fe    00 00 02 00|09 00 00 04    ................
00 00 06 66 6f 6f 62 61    72|05 00 00 05 fe 00 00    ...foobar.......
02 00                                                 ..

15.7.2. Binary Protocol Resultset Row

15.7.2.1. NULL-bitmap

A Binary Protocol Resultset Row is made up of the NULL bitmap containing as many bits as we have columns in the resultset + 2 and the values for columns that are not NULL in the Binary Protocol Value format.

ProtocolBinary::ResultsetRow:

row of a binary resultset (COM_STMT_EXECUTE)

Payload

1              packet header [00]
string[$len]   NULL-bitmap, length: (column-count + 7 + 2) / 8
string[$len]   values

Example

09 00 00 04 00 00 06 66 6f 6f 62 61 72

15.7.2.1. NULL-bitmap

The binary protocol sends NULL values as bits inside a bitmap instead of a full byte as the ProtocolText::ResultsetRow does. If many NULL values are sent, it is more efficient than the old way.

Caution

For the Binary Protocol Resultset Row the num-fields and the field-pos need to add a offset of 2. For COM_STMT_EXECUTE this offset is 0.

The NULL-bitmap needs enough space to store a possible NULL bit for each column that is sent. Its space is calculated with:

NULL-bitmap-bytes = (num-fields + 7 + offset) / 8

resulting in:

num-fields+offset	NULL-bitmap-bytes
0	0
1	1
[...]	[...]
8	1
9	2
[...]	[...]

To store a NULL bit in the bitmap, you need to calculate the bitmap-byte (starting with 0) and the bitpos (starting with 0) in that byte from the field-index (starting with 0):

NULL-bitmap-byte = ((field-pos + offset) / 8)
NULL-bitmap-bit  = ((field-pos + offset) % 8)

Example:

Resultset Row, 9 fields, 9th field is a NULL (9th field -> field-index == 8, offset == 2)

nulls -> [00] [00]

byte_pos = (10 / 8) = 1
bit_pos  = (10 % 8) = 2

nulls[byte_pos] |= 1 << bit_pos
nulls[1] |= 1 << 2;

nulls -> [00] [04]

15.7.3. Binary Protocol Value

ProtocolBinary::MYSQL_TYPE_STRING:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_BLOB:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_LONG_BLOB:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_TINY_BLOB:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_MEDIUM_BLOB:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_VARCHAR:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_VAR_STRING:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_BIT:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_DECIMAL:

See: ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_NEWDECIMAL:

Fields

value (lenenc_str) -- string

Example

03 66 6f 6f -- string = "foo"

Todo. provide example for ProtocolBinary::MYSQL_TYPE_DECIMAL and ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

ProtocolBinary::MYSQL_TYPE_LONGLONG:

Fields

value (8) -- integer

Example

01 00 00 00 00 00 00 00 -- int64 = 1

ProtocolBinary::MYSQL_TYPE_LONG:

See: ProtocolBinary::MYSQL_TYPE_INT24

ProtocolBinary::MYSQL_TYPE_INT24:

Fields

value (4) -- integer

Example

01 00 00 00 -- int32 = 1

ProtocolBinary::MYSQL_TYPE_YEAR:

See: ProtocolBinary::MYSQL_TYPE_SHORT

ProtocolBinary::MYSQL_TYPE_SHORT:

Fields

value (2) -- integer

Implemented by

Field_year::send_binary() and Protocol_binary::store_short()

Example

01 00 -- int16 = 1

ProtocolBinary::MYSQL_TYPE_TINY:

Fields

value (1) -- integer

Example

01 -- int8 = 1

ProtocolBinary::MYSQL_TYPE_DOUBLE:

MYSQL_TYPE_DOUBLE stores a floating point in IEEE 754 double precision format

first byte is the last byte of the significant as stored in C.

Fields

value (string.fix_len) -- (len=8) double

Example

66 66 66 66 66 66 24 40 -- double = 10.2

ProtocolBinary::MYSQL_TYPE_FLOAT:

MYSQL_TYPE_FLOAT stores a floating point in IEEE 754 single precision format

Fields

value (string.fix_len) -- (len=4) float

Example

33 33 23 41 -- float = 10.2

ProtocolBinary::MYSQL_TYPE_NEWDATE:

See: ProtocolBinary::MYSQL_TYPE_TIMESTAMP

ProtocolBinary::MYSQL_TYPE_DATE:

See: ProtocolBinary::MYSQL_TYPE_TIMESTAMP

ProtocolBinary::MYSQL_TYPE_DATETIME:

See: ProtocolBinary::MYSQL_TYPE_TIMESTAMP

ProtocolBinary::MYSQL_TYPE_TIMESTAMP:

type to store a DATE, DATETIME and TIMESTAMP fields in the binary protocol.

to save space the packet can be compressed:

if year, month, day, hour, minutes, seconds and micro_seconds are all 0, length is 0 and no other field is sent
if hour, minutes, seconds and micro_seconds are all 0, length is 4 and no other field is sent
if micro_seconds is 0, length is 7 and micro_seconds is not sent
otherwise length is 11

Fields

length (1) -- number of bytes following (valid values: 0, 4, 7, 11)
year (2) -- year
month (1) -- month
day (1) -- day
hour (1) -- hour
minute (1) -- minutes
second (1) -- seconds
micro_second (4) -- micro-seconds

Example

0b da 07 0a 11 13 1b 1e 01 00 00 00 -- datetime 2010-10-17 19:27:30.000 001
04 da 07 0a 11                      -- date = 2010-10-17
0b da 07 0a 11 13 1b 1e 01 00 00 00 -- timestamp

Implemented by

Protocol_binary::store()

ProtocolBinary::MYSQL_TYPE_TIME:

type to store a TIME field in the binary protocol.

to save space the packet can be compressed:

if days, hours, minutes, seconds and micro_seconds are all 0, length is 0 and no other field is sent
if micro_seconds is 0, length is 8 and micro_seconds is not sent
otherwise length is 12

Fields

length (1) -- number of bytes following (valid values: 0, 8, 12)
is_negative (1) -- (1 if minus, 0 for plus)
days (4) -- days
hours (1) -- hours
minutes (1) -- minutes
seconds (1) -- seconds
micro_seconds (4) -- micro-seconds

Example

0c 01 78 00 00 00 13 1b 1e 01 00 00 00 -- time  -120d 19:27:30.000 001
08 01 78 00 00 00 13 1b 1e             -- time  -120d 19:27:30
01                                     -- time     0d 00:00:00

Implemented by

Protocol_binary::store_time()

ProtocolBinary::MYSQL_TYPE_NULL:: stored in the NULL-bitmap only

ProtocolBinary::MYSQL_TYPE_SET:

See: ProtocolBinary::MYSQL_TYPE_GEOMETRY

ProtocolBinary::MYSQL_TYPE_ENUM:

See: ProtocolBinary::MYSQL_TYPE_GEOMETRY

ProtocolBinary::MYSQL_TYPE_GEOMETRY:: there exists no encoding for these mysql-types in the binary protocol.

15.7.4. COM_STMT_PREPARE

15.7.4.1. COM_STMT_PREPARE response

COM_STMT_PREPARE creates a prepared statement from the passed query string.

The server returns a COM_STMT_PREPARE Response which contains a statement-id which is used to identify the prepared statement.

COM_STMT_PREPARE:

create a prepared statement

Fields

command (1) -- [16] the COM_STMT_PREPARE command
query (string.EOF) -- the query to prepare

Example

1c 00 00 00 16 53 45 4c    45 43 54 20 43 4f 4e 43    .....SELECT CONC
41 54 28 3f 2c 20 3f 29    20 41 53 20 63 6f 6c 31    AT(?, ?) AS col1

Implemented by

mysqld_stmt_prepare()

Return

COM_STMT_PREPARE_OK on success, ERR_Packet otherwise

Note

As LOAD DATA isn't supported by COM_STMT_PREPARE yet, no Protocol::LOCAL_INFILE_Request is expected here. Compare this to COM_QUERY_Response.

15.7.4.1. COM_STMT_PREPARE response

If the COM_STMT_PREPARE succeeded, it sends a COM_STMT_PREPARE_OK

COM_STMT_PREPARE_OK:

Fields

status (1) -- [00] OK
statement_id (4) -- statement-id
num_columns (2) -- number of columns
num_params (2) -- number of params
reserved_1 (1) -- [00] filler
warning_count (2) -- number of warnings
param_def_block (string.var_len) -- if num_params > 0
- num_params * Protocol::ColumnDefinition
- EOF_Packet
column_def_block (string.var_len) -- if num_columns > 0
- num_colums * Protocol::ColumnDefinition
- EOF_Packet

Example

for a prepared query like SELECT CONCAT(?, ?) AS col1:

0c 00 00 01 00 01 00 00    00 01 00 02 00 00 00 00|   ................
17 00 00 02 03 64 65 66    00 00 00 01 3f 00 0c 3f    .....def....?..?
00 00 00 00 00 fd 80 00    00 00 00|17 00 00 03 03    ................
64 65 66 00 00 00 01 3f    00 0c 3f 00 00 00 00 00    def....?..?.....
fd 80 00 00 00 00|05 00    00 04 fe 00 00 02 00|1a    ................
00 00 05 03 64 65 66 00    00 00 04 63 6f 6c 31 00    ....def....col1.
0c 3f 00 00 00 00 00 fd    80 00 1f 00 00|05 00 00    .?..............
06 fe 00 00 02 00                                     ......

Example

for a query without parameters and resultset like DO 1 it is:

0c 00 00 01 00 01 00 00    00 00 00 00 00 00 00 00

Implemented by

send_prep_stmt()

15.7.5. COM_STMT_SEND_LONG_DATA

COM_STMT_SEND_LONG_DATA sends the data for a column. Repeating to send it, appends the data to the parameter.

No response is sent back to the client.

COM_STMT_SEND_LONG_DATA:

COM_STMT_SEND_LONG_DATA
  direction: client -> server
  response: none

  payload:
    1              [18] COM_STMT_SEND_LONG_DATA
    4              statement-id
    2              param-id
    n              data

COM_STMT_SEND_LONG_DATA has to be sent before COM_STMT_EXECUTE.

15.7.6. COM_STMT_EXECUTE

15.7.6.1. COM_STMT_EXECUTE Response

COM_STMT_EXECUTE asks the server to execute a prepared statement as identified by stmt-id.

It sends the values for the placeholders of the prepared statement (if it contained any) in Binary Protocol Value form. The type of each parameter is made up of two bytes:

the type as in Protocol::ColumnType
a flag byte which has the highest bit set if the type is unsigned [80]

The num-params used for this packet has to match the num_params of the COM_STMT_PREPARE_OK of the corresponding prepared statement.

The server returns a COM_STMT_EXECUTE Response.

COM_STMT_EXECUTE:

COM_STMT_EXECUTE
  execute a prepared statement

  direction: client -> server
  response: COM_STMT_EXECUTE Response

  payload:
    1              [17] COM_STMT_EXECUTE
    4              stmt-id
    1              flags
    4              iteration-count
      if num-params > 0:
    n              NULL-bitmap, length: (num-params+7)/8
    1              new-params-bound-flag
      if new-params-bound-flag == 1:
    n              type of each parameter, length: num-params * 2
    n              value of each parameter

  example:
    12 00 00 00 17 01 00 00    00 00 01 00 00 00 00 01    ................
    0f 00 03 66 6f 6f                                     ...foo

The iteration-count is always 1.

The flags are:

flags	constant name
0x00	CURSOR_TYPE_NO_CURSOR
0x01	CURSOR_TYPE_READ_ONLY
0x02	CURSOR_TYPE_FOR_UPDATE
0x04	CURSOR_TYPE_SCROLLABLE

NULL-bitmap is like NULL-bitmap for the Binary Protocol Resultset Row just that it has a bit-offset of 0.

15.7.6.1. COM_STMT_EXECUTE Response

Similar to the COM_QUERY_Response a COM_STMT_EXECUTE either returns:

a OK_Packet
a ERR_Packet
or a resultset: Binary Protocol Resultset

15.7.7. COM_STMT_CLOSE

COM_STMT_CLOSE deallocates a prepared statement

No response is sent back to the client.

COM_STMT_CLOSE:

COM_STMT_CLOSE
  direction: client -> server
  response: none

  payload:
    1              [19] COM_STMT_CLOSE
    4              statement-id

  example:
    05 00 00 00 19 01 00 00    00                         .........

15.7.8. COM_STMT_RESET

COM_STMT_RESET resets the data of a prepared statement which was accumulated with COM_STMT_SEND_LONG_DATA commands.

The server will send a OK_Packet if the statement could be reset, a ERR_Packet if not.

COM_STMT_RESET:

COM_STMT_RESET
  direction: client -> server
  response: OK or ERR

  payload:
    1              [1a] COM_STMT_RESET
    4              statement-id

  example:
    05 00 00 00 1a 01 00 00    00                         .........

Top / Previous / Next / Up / Table of Contents