Explain the correct use of the string in the MySQL database

This article mainly introduces the practical steps of character (string) in MySQL database. We all know that the character (string) of MySQL database is often used in practice, so you know more about it is still good Hopefully this will bring you some help in this regard.

MySQL database character (string) class Do not think that the character type is CHAR CHAR and VARCHAR difference is that CHAR is a fixed length, as long as you define a field is CHAR (10), no matter whether your stored data reached 10 bytes , It takes up 10 bytes of space; VARCHAR is variable length, if a field may not be fixed length value, we only know that it can not exceed 10 characters, it is defined as VARCHAR (10) is the most cost-effective, VARCHAR space occupies the actual length of its value +1.

Why do you want to +1 this byte is used to save the actual length of how much. From this +1 also should see that if a field, its possible value up to 10 characters, but in most cases is the use of 10 characters, the use of VARCHAR is not worth it: Because in most cases , The actual space occupied is 11 bytes, take up more than one byte more than CHAR (10).

For example, a table that stores the name and code of a stock. The name of the stock is mostly four words, that is, eight bytes. The stock code is six digits in Shanghai and four digits in Shenzhen. These are fixed-length, of course, the name of the stock to use CHAR (8); stock code is not fixed length, but if you use VARCHAR (6), a Shenzhen stock code actually occupies 5 bytes of space, and a Shanghai Of the stock code to take 7 bytes! Considering the number of stocks in Shanghai and Shenzhen more, then the use of VARCHAR (6) is not as good as CHAR (6).

Although the maximum length of a CHAR or VARCHAR can be 255, I think CHAR is greater than 20 is almost impossible to use - there is little more than 20 bytes in length fixed length of the stuff? Not a fixed length VARCHAR . VARCHAR greater than 100 is almost impossible to use - more than this with TEXT just fine.

TINYTEXT, the maximum length of 255, the actual space is also occupied space +1; TEXT, the maximum length of 65535, space is the actual length +2; MEDIUMTEXT, the maximum length of 16777215, space is the actual length +3; LONGTEXT, the maximum length of 4294967295, The space is the actual length +4. Why + 1, +2, +3, +4? If you do not know if you should play PP. These can be used in forums, news, ah, what, to save the body of the article. Depending on the actual situation, choose different types from small to large.

The above is related to the MySQL database character (string) type of introduction, hope you can gain something.