Binary Protocol Resultset

Binary Protocol Resultset is similar to the Text Resultset.

It just contains the rows in Binary Protocol Resultset Row format.

ProtocolBinary::Resultset:

Type	Name	Description
int<lenenc>	column_count	always grater than 0
column_count * Column Definition
None or many Binary Protocol Resultset Row
EOF_Packet

Note

if CLIENT_DEPRECATE_EOF client capability flag is set, OK_Packet is sent, else EOF_Packet is sent.

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

Binary Protocol Resultset Row

A Binary Protocol Resultset Row is made up of a 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:

Type	Name	Description
int<1>	packet_header	[0x00] packer header
binary<var>	null_bitmap	NULL bitmap, length= (column_count + 7 + 2) / 8
binary<var>	values	values for non-null columns

Example

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

NULL-Bitmap

The binary protocol sends NULL values as bits inside a bitmap instead of a full byte as the Text Resultset Row 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 index_field (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]

Binary Protocol Value

ProtocolBinary::MYSQL_TYPE_STRING, ProtocolBinary::MYSQL_TYPE_VARCHAR, ProtocolBinary::MYSQL_TYPE_VAR_STRING, ProtocolBinary::MYSQL_TYPE_ENUM, ProtocolBinary::MYSQL_TYPE_SET, ProtocolBinary::MYSQL_TYPE_LONG_BLOB, ProtocolBinary::MYSQL_TYPE_MEDIUM_BLOB, ProtocolBinary::MYSQL_TYPE_BLOB, ProtocolBinary::MYSQL_TYPE_TINY_BLOB, ProtocolBinary::MYSQL_TYPE_GEOMETRY, ProtocolBinary::MYSQL_TYPE_BIT, ProtocolBinary::MYSQL_TYPE_DECIMAL, ProtocolBinary::MYSQL_TYPE_NEWDECIMAL:

MYSQL_TYPE_STRING

Type	Name	Description
string<lenenc>	value	String

Example

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

ProtocolBinary::MYSQL_TYPE_LONGLONG

MYSQL_TYPE_LONGLONG

Type	Name	Description
int<8>	value	integer

Example

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

ProtocolBinary::MYSQL_TYPE_LONG, ProtocolBinary::MYSQL_TYPE_INT24

MYSQL_TYPE_LONG, MYSQL_TYPE_INT24

Type	Name	Description
int<4>	value	integer

Example

01 00 00 00 -- int32 = 1

ProtocolBinary::MYSQL_TYPE_SHORT, ProtocolBinary::MYSQL_TYPE_YEAR

MYSQL_TYPE_SHORT, MYSQL_TYPE_YEAR

Type	Name	Description
int<2>	value	integer

Example

01 00 -- int16 = 1

ProtocolBinary::MYSQL_TYPE_TINY

MYSQL_TYPE_TINY

Type	Name	Description
int<1>	value	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.

MYSQL_TYPE_DOUBLE

Type	Name	Description
string[8]	value	a IEEE 754 double precision format (8 bytes) 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.

MYSQL_TYPE_FLOAT

Type	Name	Description
string[4]	value	a IEEE 754 single precision format (4 bytes) float

Example

33 33 23 41 -- float = 10.2

ProtocolBinary::MYSQL_TYPE_DATE, ProtocolBinary::MYSQL_TYPE_DATETIME, ProtocolBinary::MYSQL_TYPE_TIMESTAMP:

Type to store a MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and MYSQL_TYPE_TIMESTAMP fields in the binary protocol.

To save space the packet can be compressed:

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

MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and MYSQL_TYPE_TIMESTAMP

Type	Name	Description
int<1>	length	number of bytes following (valid values: 0, 4, 7, 11)
int<2>	year	year
int<1>	month	month
int<1>	day	day
int<1>	hour	hour
int<1>	minute	minute
int<1>	second	second
int<4>	microsecond	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

ProtocolBinary::MYSQL_TYPE_TIME

Type to store a MYSQL_TYPE_TIME field in the binary protocol.

To save space the packet can be compressed:

• if day, hour, minutes, seconds and microseconds are all 0, length is 0 and no other field is sent.
• if microseconds is 0, length is 8 and micro_seconds is not sent.
• otherwise the length is 12

MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and MYSQL_TYPE_TIMESTAMP

Type	Name	Description
int<1>	length	number of bytes following (valid values: 0, 8, 12)
int<1>	is_negative	1 if minus, 0 for plus
int<4>	days	days
int<1>	hour	hour
int<1>	minute	minute
int<1>	second	second
int<4>	microsecond	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

ProtocolBinary::MYSQL_TYPE_NULL

stored in the NULL-Bitmap only