They are stored in different ways and data are retrieved differently.
Data retrieval efficiency is: char > VarChar > Text
Space occupancy, it is necessary to specifically analyze the specific situation.
- Char: It is convenient to store the fixed-length data, the index on the Char field is highly efficient, you must define the length in parentheses, you can have a default value, such as a char (10), so no matter whether your stored data reaches 10 bytes, it takes up 10 bytes of space (automatically filled with spaces), And the space behind the search will be hidden, so the retrieved data need to remember what trim and other functions to filter the space.
- VARCHAR: Stores variable-length data, but the storage efficiency is no higher than char, you must define the length in parentheses, and you can have a default value. When you save the data, no spaces are automatically populated, and when the data has spaces, the trailing spaces remain when the value is saved and retrieved. In addition, the actual length of the varchar type is +1 of the actual length of its value, which is used to hold the length that is actually 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, the storage or retrieval process, there is no case conversion, after the specified length, will not report an error, but this length is not working, meaning that when you insert data, more than you specify the length or can be inserted normally.
About storage space:
When using the UTF8 character set, this is what the manual says:
- The basic Latin alphabet, numbers, and punctuation characters use one byte;
- Most European and Middle Eastern handwritten letters are suitable for two byte sequences: extended Latin alphabet (including pronunciation symbols, macron, accents, bass symbols and other notes), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and other languages;
- Korean, Chinese, and Japanese hieroglyphs use a three-byte sequence.
Conclusion:
- frequently changing fields with varchar;
- Know the fixed length with char;
- Use varchar as much as possible;
- More than 255 bytes can only be used with varchar or text;
- can use varchar where the text is not used;
- The ability to select numeric types instead of string types (phone numbers) with fields of numeric type can reduce query and connection performance and increase storage overhead. This is because the engine is processing the query and connecting back to each character in a string by comparison, and for a digital type it only needs to be compared once.
The difference between char, varchar, and text in MySQL
