Differences between field types char varchar and text in MySQL

In MySQL, fields of type char, varchar, and text can all be used to store data of character type. char, varchar can all specify the maximum character length, but text cannot.

Their storage methods and data retrieval methods are also different.

The retrieval efficiency of data is: char > varchar > text

Specific instructions:

char: It is convenient to store fixed-length data, The index efficiency level on the CHAR field is high, The length must be defined in parentheses, and there can be default values, such as char (10), so no matter whether the data you store reaches 10 bytes or not, it will occupy 10 bytes of space (automatically filled with spaces), and the spaces behind it will be hidden when retrieving, so the retrieved data needs to remember what functions such as trim are used to filter spaces.

varchar: Stores variable-length data, but the storage efficiency is not as high as CHAR. The length must be defined in parentheses, and there can be default values. When saving data, there is no automatic filling of spaces, and if there are spaces in the data, the spaces at the end will remain when saving and retrieving the values. In addition, the actual length of the varchar type is the actual length of its value +1, which is used to hold how much length is actually used.

text: Stores variable-length non-Unicode data with a maximum length of 2 ^ 31-1 characters. text column can not have a default value, storage or retrieval process, there is no case conversion, later if specified length, will not report an error, but this length is not effective, which means that when you insert data, more than the length you specified can still be inserted normally.

About storage space:

When using the UTF8 character set, it is described in the MySQL manual as follows:

Basic Latin letters, numbers and punctuation marks use 1 byte; Most European and Middle Eastern handwritten alphabets fit in a sequence of two bytes: extended Latin alphabets (including pronunciations, macrons, accents, bass and other notes), Cyrillic alphabets, Greek, Armenian, Hebrew, Arabic, Syriac and other languages; Korean, Chinese, and Japanese hieroglyphs use 3-byte sequences.

Conclusions:

1. Use varchar for fields that often change;

2. Use char; for those with fixed length;

3. Only varchar or text can be used for those exceeding 255 bytes;

4. text is not used where varchar can be used;

5. Be able to use numeric fields to choose numeric types instead of string types as much as possible, which will reduce the performance of queries and joins and increase the storage overhead. This is because the engine is processing queries and concatenating back to compare every 1 character in the string one by one, whereas for numeric types only one comparison is sufficient;

6. There are multiple large fields in the same table. If you can merge them, try to merge them. If you can't merge them, consider dividing them into tables. Please optimize the storage efficiency of BLOB and TEXT columns in InnoDB table

Summarize