Personal understanding of text, varchar, char in terms of storage length _ MySQL

Text, varchar, char personal understanding of storage length this document is limited to mysql 5.5 or above

Summary in utf8 format

Mysql provides the following description in mysql official documentation:

1. refer to the following description in mysql5.1 Reference Manual:

TEXT [(M)]

A text column with a maximum length of 65,535 (216-1) characters.

Optional lengthM. MySQL creates the smallest column but is sufficient to accommodateMThe TEXT type of the character length value.

[NATIONAL] VARCHAR (M) [BINARY]

Variable-length string.MMaximum column length.MThe range is 0 to 65,535. (The maximum actual length of a VARCHAR is determined by the maximum row size and the character set used. MaxValidLength is 65,532 bytes ).

[NATIONAL] CHAR (M) [BINARY | ASCII | UNICODE]

Fixed-length string. when saved, fill in spaces on the right side to reach the specified length.MColumn length.MThe range is 0 to 255 characters.

Limit 2 limit describes the relationship between characters and bytes.

The 1-digit character refers to the letters, numbers, words, and symbols used in the computer.

2 bytes in ASCII encoding, one English letter character storage needs 1 byte

3 bytes: in GB 2312 or GBK encoding, the storage of one Chinese character requires 2 bytes

4 bytes in UTF-8 encoding, an English letter character storage requires 1 byte, it takes 3 to 4 bytes to store a Chinese character (in my computer environment, it takes 3 bytes to store a Chinese character)

5 bytes in UTF-16 encoding, an English letter or a Chinese character storage requires 2 bytes (Unicode extended area of some Chinese character storage requires 4 bytes)

6 bytes in UTF-32 encoding, the storage of any character in the world needs 4 bytes

7 bytes: Currently, the database uses the UTF-8 encoding format, so one character occupies three bytes.

3. However, through the experiment, we have obtained different values (in utf8 encoding) from the preceding manual document)

1. up to 65535 bytes can be stored for the plain Text type, instead of 65535 characters. 65535 characters can be stored for English and other characters, and up to 21485 Chinese characters can be stored for pure Chinese characters.

2. the maximum value of the Varchar type can be set to 21491, and the storage format is character. varchar (21491) can store up to 21491 English characters, or up to 21491 Chinese characters.

3. a maximum of 255 characters can be set for the Char type, a maximum of 255 English characters can be stored for char (255), a maximum of 255 Chinese characters can be stored, and a maximum of characters can be stored for char (M) when char is used, the space occupied by this field is M characters no matter how many values it occupies.

4. efficiency: char> varchar> text

4.1.4 using the above discussion, we can design a principle for database design.

1. when the database stores html or text that does not need to be retrieved, the database requires a large amount of storage and retrieval. the best storage performance ., can occupy less space and store more content

2. when some characters are relatively small and require high retrieval efficiency, we should first consider char. for example, the type of the dictionary table can be char, which will greatly improve the reading efficiency of the dictionary table.

3. except for the above special characters, we recommend that you use varchar for all other fields, but set different lengths for different fields. for example, you can use varchar (200) for the title) whether it is English or Chinese, the title should be enough.

4. for some similar things such as personal profile and medical profile, different lengths need to be allocated according to different requirements. Therefore, we use varchar to define the length according to requirements.