Shulou(Shulou.com)06/02 Report--

What this article shares with you is an example analysis of the format and basic types of mysql messages. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Navicat and mysql are a pair of good friends, and they communicate very frequently every day. The host writes down every sql statement on navicat and gently clicks the execute button. Navicat quickly transmits the master's instructions to mysql, and mysql immediately sends the return results back to navicat to show to the owner. The host is satisfied with their efficiency, and the host is a little curious: how do you two gay friends communicate? What should I do if I use the program to interact directly with mysql without going through navicat?

Mysql said with a smile: this is our secret.

Navicat is a little unhappy: does the master want to abandon me? Whoa, whoa.

The host comforted: you are thinking too much, I just want to know more about you.

Then I'll tell you more about our little secret, mysql said leisurely:

If you want to interact through me, you have to follow the format I set. Everyone can't come over and speak their own dialect. I don't understand. When you come to my place, you have to follow my rules.

1. All the messages I send you are in this format:

The message format of the server-> client

The first 3 bytes payload_length represents the actual length of the following data, and the middle byte sequence_id represents the sequence number of the message, starting from 0, adding 1 each time, and the last paylaod is the content I sent you.

You may also have found that payload_length is represented by 3 bytes, and the maximum can only represent 224 − 1 bytes is also 16m. If the message is greater than or equal to 16m, it will be split into two messages.

For example, it happens to send a 16m message:

Ff ff ff 00 (data field 16m)... 00 00 00 01 (data field is empty)

The data field of the second message is empty.

two。 The message sent to me by the client should also be in a certain format:

Client-> the message format of the server

The first byte represents the type of command

The following bytes indicate specific commands, such as the long message that closes the connection:

After this regulation, we will have a common language, and we will be able to understand what each other is saying, so it is very important to say that we have a common language.

The host carefully reviewed what mysql just said and found that there was another place he didn't understand and asked, "what does int,string mean in the message you sent me?"

Mysql suddenly realized that he had forgotten to mention one important thing: oops, I forgot to tell you about the basic data types.

Mysql has two basic data types:

Integers (integer) and String (string)

Integers (integer type) is divided into fixed length integer type (Fixed-length integers) and variable length integer type (Length-encoded integers).

1. The fixed-length integer type (Fixed-length integers) can represent the length of 1, 2, 3, 4, 4, 6, and 8 bytes respectively:

Int means a length of 1 byte, int means a length of 2 bytes, and so on, for example, int means 1 is like this: 01 00 00

By the way, the bytes in the mysql protocol are represented as LittileEndian, with the low-order bytes in front, accounting for a total of 3 bytes, so the first byte is 01, followed by 00 00.

two。 Variable length integer type (Length-encoded integers)

If the number is less than 251, it is represented by one byte = 1 byte

If the number ≥ 251and < 216is represented by fc + 2 bytes = 3 bytes

If the number ≥ (216) and < (224), use fd + 3 bytes for = 4 bytes

If the number ≥ (224) and < (264), use fe + 8 bytes to represent = 9 bytes

The owner said it was strange: a byte can represent at most 255 numbers, why can't it be expressed directly to 255? But it only means 250?

Mysql smiled and said: because 251252253254255 has been requisitioned by me, they represent special uses:

251 (fb): indicates NULL

252 (fc): indicates that the size of the value accounts for the next 2 bytes

253 (fd): indicates that the size of the value accounts for the next 3 bytes

254 (fe): indicates that the size of the value accounts for the next 8 bytes

255( ff): indicates an error message ERR packet

Let's talk about String (string) types:

1. Variable length character type (LengthEncodedString): the same as the above variable length integer type, judging the length of the occupied byte according to the value of the first byte

two。 Fixed-length string type (FixedLengthString): the following number represents the length of the bytes occupied by the string

3. Dynamic calculation length (VariableLengthString): the dynamic calculation length based on var

4. Package end string mode (RestOfPacketString): I don't understand.

5. Null-terminated string type (NulTerminatedString): string ends with 00

So, the host suddenly enlightened, looking at navicat and mysql are a lot of cordial, know how they are talking to each other, an evil idea slowly rose from the bottom of my heart, hey, then I can monitor the conversation between them, ha.

The above is an example analysis of the format and basic types of mysql messages. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.