The difference between char, varchar and text field types in MySQL

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

The way they store and retrieve data is also different.

The data retrieval efficiency is: char > varchar > text

Specific instructions:

char: It is very convenient to store fixed-length data. The index on the CHAR field is highly efficient. The length must be defined in parentheses and can have a default value. For example, if you define char(10), then regardless of whether the data you store reaches 10 bytes, it will take up 10 bytes of space (automatically filled with spaces), and the trailing spaces will be hidden when searching. Therefore, you need to remember to use functions such as trim to filter out spaces in the retrieved data.

varchar: stores variable-length data, but the storage efficiency is not as high as CHAR. The length must be defined in parentheses and can have a default value. When saving data, no automatic space filling is performed, and if there are spaces in the data, the trailing spaces will still be retained when the value is saved and retrieved. In addition, the actual length of the varchar type is the actual length of its value + 1. This byte is used to save the actual length used.

text: Stores variable-length non-Unicode data with a maximum length of 2^31-1 characters. The text column cannot have a default value. There is no case conversion during storage or retrieval. If you specify a length later, no error will be reported, but this length will have no effect. This means that when you insert data, it can still be inserted normally even if it exceeds the length you specified.

About storage space:

When using the UTF8 character set, the MySQL manual describes it this way:

• Basic Latin letters, numbers, and punctuation use one byte;
• Most European and Middle Eastern scripts fit into a two-byte sequence: the extended Latin alphabet (including diacritics, macrons, grave accents, umlauts, and other diacritics), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others;
• Korean, Chinese, and Japanese ideographic characters use three-byte sequences.

in conclusion:

1. Use varchar for fields that change frequently;

2. If you know the fixed length, use char;

3. For data exceeding 255 bytes, only varchar or text can be used;

4. Don't use text where varchar can be used;

5. If you can use numeric fields, try to use numeric types instead of string types. This will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in the string one by one when processing the query and connection, and for numeric types, only one comparison is enough;

6. If there are multiple large fields in the same table, try to merge them if possible. If they cannot be merged, consider splitting the table. For the reasons, please refer to Optimizing the Storage Efficiency of BLOB and TEXT Columns in InnoDB Tables.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links