Detailed description of the correct use of strings in the MySQL database

This article mainly introduces the actual operation steps of MySQL database characters (strings). We all know that MySQL database characters (strings) are often used in actual operations, so you have a better understanding of it and hope to bring you some help in this aspect.

Do not assume that the character type is CHAR in the MySQL database. The difference between CHAR and VARCHAR is that CHAR is of fixed length, as long as you define a field as CHAR (10 ), therefore, no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space, while VARCHAR is of a variable length, if the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10, the occupied space of the VARCHAR type is the actual length of its value plus 1.

Why + 1? This byte is used to save the actual length. From this + 1, we can also see that if a field has a maximum of 10 characters, and in most cases, it uses 10 characters, it is not feasible to use VARCHAR: In most cases, the actual occupied space is 11 bytes, occupying one more byte than CHAR (10.

For example, a table stores the stock name and code. The stock name is mostly in four words, that is, 8 bytes. The stock code is a six-digit number in Shanghai, shenzhen has four digits. These are fixed length, and the stock name must use CHAR (8). Although the stock code is not fixed length, if VARCHAR (6) is used ), A stock code in Shenzhen actually occupies 5 bytes, while a stock code in Shanghai occupies 7 bytes! Considering that Shanghai has more shares than Shenzhen, VARCHAR (6) is not as good as CHAR (6.

Although a CHAR or VARCHAR can have a maximum length of 255, I think that a CHAR larger than 20 is almost useless-there are very few fixed lengths larger than 20 bytes, right? Use VARCHAR if it is not a fixed length. VARCHAR with a value greater than 100 is almost useless-it is better to use TEXT with a larger value.

TINYTEXT, the maximum length is 255, the occupied space is also the actual length + 1; TEXT, the maximum length of 65535, the occupied space is the actual length + 2; MEDIUMTEXT, the maximum length of 16777215, the occupied space is the actual length + 3; LONGTEXT, the maximum length is 4294967295, And the occupied space is the actual length + 4. Why + 1, + 2, + 3, + 4? If you do not know, you should play PP. These can be used in forums, news, or something to save the text of an article. Select different types from small to large based on the actual situation.

The above content is an introduction to the character (string) Type of the MySQL database. I hope you will have some gains.

Image 1

The above content describes the character (string) Type of the MySQL database, hoping to help you in this regard.