MySQL communication protocol research 3 (Text mode query)

Today, let's look at the query in Text mode. The so-called Text mode means that SQL only contains Text and has no parameters. Of course, this query SQL can be select or update. The basic process is as follows:

Client               Server  |   COM_QUERY + sql  |  |------------------->|  |       response     |  |<-------------------|  |     column define  |  |<-------------------|  |     column define  |  |<-------------------|  |         EOF        |  |<-------------------|  |       data row     |  |<-------------------|  |       data row     |  |<-------------------|  |       data row     |  |<-------------------|  |         EOF        |  |<-------------------|

1. COM_QUERY

Format:

1              [03] COM_QUERYstring[EOF]    the query the server shall execute

Packet Capture:

00000000  12 00 00 00 03 53 45 4C  45 43 54 20 2A 20 46 52   .....SEL ECT * FR00000010  4F 4D 20 65 70 00 01 00  00 00 01                  OM ep... ...

Resolution:

12 00 00 // Data Length, 3 bytes, 0x12 = 18 bytes 00 // serial number, 1 byte, because it is a new round of interaction, it is also reset to 0 03 // COM_QUERY identifier, 1 byte, fixed to 0x0353 45 4C 45 43 54 20 2A 20 46 52 4F 4D 20 65 70 00 // SQL statement, end with \ 0, content = SELECT * FROM ep

2. Query Response

This response may be an OK Packet or an ERR Packet. Here we will look at the data.

Format:

lenenc-int     column count

Packet Capture:

00000000  01 00 00 01 03                                     .....

Resolution:

01 00 00 // Data Length, 3 bytes, 0x01 = 1 byte 01 // serial number, 1 byte 03 // number of fields, extended integer, 0x03 = 3

Variable-Length Integer:

<251, 1 byte storage ≥ 251 & <(2 ^ 16), 0xfc + 2 byte storage ≥ (2 ^ 16) & <(2 ^ 24) when 0xfd + 3 bytes are stored ≥ (2 ^ 24) & <(2 ^ 64), 0xfe + 8 bytes are stored

3. Column Define

Format: (Column Definition Packet)

lenenc_str     cataloglenenc_str     schemalenenc_str     tablelenenc_str     org_tablelenenc_str     namelenenc_str     org_namelenenc_int     length of fixed-length fields [0c]2              character set4              column length1              type2              flags1              decimals2              filler [00] [00]  if command was COM_FIELD_LIST {lenenc_int     length of default-valuesstring[$len]   default values  }

Packet Capture:

00000000  27 00 00 02 03 64 65 66  05 76 6D 6E 70 6E 02 65  '....def .vmnpn.e00000010  70 02 65 70 04 47 55 49  44 04 47 55 49 44 0C 21  p.ep.GUI D.GUID.!00000020  00 6C 00 00 00 FD 03 50  00 00 00                 .l.....P ...

Resolution:

27 00 00 // Data Length, 3 bytes, 0x27 = 39 bytes 02 // serial number, 1 byte 03 64 65 66 // catelog, variable length string, content = def05 76 6D 6E 70 6E // schema, variable-length string, content = vmnpn02 65 70 // table name, variable-length string, content = ep02 65 70 // original table name, variable-length string, content = ep04 47 55 49 44 // field name, variable-length string, content = GUID04 47 55 49 44 // Original Field name, variable-length string, content = GUID0C // fixed to 0x0c21 00 // character set, 2 bytes, 0x21 = 33 = utf8_general_ci6C 00 00 00 // field length, 4 bytes, 0x6c = 108, the corresponding field is char36 in utf8 format, so it occupies 108 bytes of FD // data type, 1 byte, 0xfd = 253 = VAR_STRING03 50 // flags, 2 byte 00 // decimals, 1 byte 00 00 // fixed padding 2 byte 0x00

Variable-length string:

Lenenc_int String Length nstring [n] fixed length string

4. EOF

Format: (EOF Packet)

1              [fe] the EOF header  if capabilities & CLIENT_PROTOCOL_41 {2              warning count2              status flags  }

Packet Capture:

00000000  05 00 00 05 FE 00  00 22 00                      ...... ...

Resolution:

05 00 00 // Data Length, 3 bytes, 0x05 = 5 bytes 05 // serial number, 1 byte FE // EOF identifier, 1 byte, fixed to 0xfe00 00 // warning count, 2 bytes 22 00 // status, 2 bytes

5. Data

Format: (Text Resultset Row Packet)

NULL is sent as 0xfbeverything else is converted into a string and is sent as Protocol::LengthEncodedString.

Packet Capture:

00000000  3D 00 00 06 24 34 32 32  34 30 36 32 32 2D 62 30  =...$422 40622-b000000010  38 64 2D 65 38 30 61 2D  32 66 65 37 2D 66 33 37  8d-e80a- 2fe7-f3700000020  32 37 66 62 62 66 63 33  33 09 44 73 61 2D 31 32  27fbbfc3 3.Dsa-1200000030  2E 34 35 0D 31 39 33 2E  31 36 38 2E 31 32 2E 34  .45.193. 168.12.400000040  35                                                5

Resolution:

3D 00 00 // Data Length, 3 bytes, 0x3d = 61 bytes 06 // serial number, 1 byte 24 // 1st Field Data Length, extended integer, 0x24 = 36 bytes 34 32 ...... 66 63 33 33 // 1st field data, content = 42240622-b08d-e80a-2fe7-f3727fbbfc3309 // Data Length of 2nd fields, variable length shaping, 0x09 = 9 byte 44 73 61 2D 31 32 2E 34 35 // 2nd field data, content = Dsa-12.450D // 3rd Field Data Length, Variable Length Integer, 0x0d = 13 bytes 31 39 33 2E 31 36 38 2E 31 32 2E 34 35 // 3rd Field Data, content = 193.168.12.45

To be continued ......

This article from the rabbit nest blog, please be sure to keep this source http://boytnt.blog.51cto.com/966121/1286820