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

Editor to share with you how to store null values on disk based on MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

1 Why can't I just save a NULL?

NULL value list. The possible field value in a row of data is NULL, such as the nickname field, which is allowed to be NULL. When storing, if no value is assigned, the field value is NULL.

Isn't it a waste of storage space and strange to assume that the NULL value of this field is stored as a "NULL" string when it is stored on disk?

2 how to store it?

Not through the string, but through the binary bit bit storage, a row of data assuming that the values of multiple fields are NULL, then the NULL of these fields will be stored in the null value list in the form of the bit bit.

The table is as follows:

CREATE TABLE customer (name VARCHAR (10) NOT NULL, address VARCHAR (20), gender CHAR (1), job VARCHAR (30), school VARCHAR (50) ROW_FORMAT=COMPACT

There are four variable length fields and a fixed length field. Name declares NOT NULL, and the other four fields may be NULL.

How is the following line of data stored on disk?

Jack NULL m NULL xx_school

Both fields are NULL.

Disk storage format of 3 rows of data

Consider how the row of case data in the table above is stored on disk, because it has multiple variable-length fields and multiple fields that are allowed to be NULL. First of all, let's review that a row of data should be stored on disk in the following format:

Variable length field length list NULL values list header information column1=value1 column2=value2. ColumnN=valueN

4 variable length fields, put the length of the school field in reverse order, and then put the value length of the job, address and name fields?

But to distinguish a problem, if the variable length field value is NULL, there is no need to store its value length in the variable length field length list, so in the above row of data, only two variable length fields, name and school, have values. Put their length in the variable length field length list in reverse order:

0x09 0x04 NULL values list header information column1=value1 column2=value2. ColumnN=valueN

All fields with an allowed value of NULL do not have to be NULL. As long as they are allowed to be NULL, each field has a binary bit bit value:

A bit value of 1 means NULL.

If the bit value is 0, it is not NULL.

For example, the above four fields are allowed to be NULL, and everyone will have a bit bit. The value of this row of data is

Jack NULL m NULL xx_school

Two of the fields are null,2 and the other is not null, so the 4 bit bits should be: 1010

However, when it is actually placed in the null values list, it is placed in reverse order, so the null values list contains: 0101. The overall row of data is as follows:

0x09 0x04 0101 header information column1=value1 column2=value2. ColumnN=valueN

When the actual null value list is stored, it is generally possible to have a multiple of at least 8 bit bits, and if the number of bit bits is less than 8, the higher order is 0, so the actual storage is as follows:

0x09 0x04 00000101 header information column1=value1 column2=value2. ColumnN=valueN

4 how to read a row of data from the disk?

Disk data storage format:

0x09 0x04 00000101 header information column1=value1 column2=value2. ColumnN=valueN

Read first:

Variable length field length list

Just know that there are several variable length fields.

NULL values list

Which variable length fields are NULL:

Parse the value length of a variable-length field that is not NULL from the list of variable-length fields, and then know which fields are NULL. Based on this information, you can read the value of each field from the actual column value storage area.

The value of the variable length field is read by the length of the value. If it is NULL, you will know that it is a NULL and there is no value store.

Fixed length field, read by fixed length

These are all the contents of the article "how to store null values on disk based on MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.