They are stored differently and their data is retrieved differently. Data retrieval efficiency: char > varchar > text

1. The char: Fixed-length data storage is very convenient, CHAR field grade indexes on the efficiency high, must be defined in parentheses length, can have default values, such as the definition of CHAR (10), so whether you store the data reached 10 characters, all want to take up the space of 10 characters with a blank filling (to be automatic), and the Spaces will be hidden behind the retrieval time, Remember to use a trim function to filter whitespace from the retrieved data.

2. Varchar: stores variable length data, but not as efficiently as CHAR. When data is saved, no Spaces are automatically filled in, and if data has Spaces, the trailing Spaces are retained when values are saved and retrieved. In addition, the actual length of the vARCHAR type is the actual length of its value +1, a byte that holds how much length was actually used.

3. Text: Stores non-Unicode data of variable length, up to 2^31-1 characters. The text column does not have a default value. There is no case conversion during storage or retrieval. If you specify a length, you will not get an error.

About storage space:

When using the UTF8 character set, the manual states:

Basic Latin letters, numbers, and punctuation uses one byte; Most European and Middle Eastern handwritten alphabets fit into two-byte sequences: extended Latin alphabets (including phonetic symbols, long notes, accent symbols, low notes, and other notes), Cyrillic alphabets, Greek, Armenian, Hebrew, Arabic, Syriac, and other languages; Korean, Chinese, and Japanese hieroglyphs use three-byte sequences.

Conclusion:

  • Varchar is used for fields that change frequently;
  • Use char if you know the fixed length;
  • Use vARCHAR whenever possible;
  • The value larger than 255 bytes can only be vARCHar or text.
  • Text is not used where varchar is used;
  • The ability to use numeric fields to select numeric types rather than string (phone numbers) reduces query and join performance and increases storage overhead. This is because the engine is processing queries and concatenating back to compare each character in the string one by one, whereas for numeric types it only needs to compare once.

Article source: http://www.cnblogs.com/xianDan/p/4292706.html