Distinction between CHAR and VARCHAR in MySQL strings

We are familiar with MySQL strings. The following describes the differences between CHAR and VARCHAR in MySQL strings. We hope this will help you learn MySQL strings.

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.

MySQL string truncation Function Method

Differences between MySQL string column types

MySQL replaces the specified field string in batches

Usage of MySQL string connection functions

Mysql query case