Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between char, varchar and text in MySQL. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope you can gain something according to this article.

Details:

char： It is very convenient to store fixed-length data. The index efficiency on the CHAR field is high. The length must be defined in parentheses. There can be a default value. For example, char(10) is defined. Then, no matter whether the data you store reaches 10 bytes or not, it will take up 10 bytes of space (automatically filled with spaces), and the spaces behind will be hidden during retrieval. Therefore, the retrieved data needs to remember what trim functions to filter spaces.

varchar: Store 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 data is saved, spaces are not autofilled, and if spaces exist in the data, trailing spaces are retained when data is saved and retrieved. In addition, the actual length of the varchar type is the actual length of its value +1, which is used to store how much length is actually used.

text: Stores variable-length non-Unicode data, up to a maximum length of 2^31-1 characters. Text column cannot have default value. There is no case conversion during storage or retrieval. If you specify the length later, no error will be reported. However, this length does not work. It means that when you insert data, it can be inserted normally beyond the length you specify.

About Storage Space:

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

Basic Latin letters, numerals and punctuation use one byte;

Most European and Middle Eastern handwritten letters fit into two byte sequences: extended Latin letters (including diacritics, diacritics, accents, bass marks, and other notes), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syria, and other languages;

Korean, Chinese and Japanese pictographs use a sequence of three bytes.

Conclusion:

1. Varchar is used for frequently changing fields;

2, know the fixed length with char;

More than 255 bytes can only be used varchar or text;

4, where you can use varchar without text;

5, be able to use numeric type fields as far as possible to choose numeric type instead of string type, which will reduce the performance of queries and connections, and will increase storage overhead. This is because the engine compares each character in the string individually while processing queries and concatenations, whereas only one comparison is sufficient for numeric types;

6. Multiple large fields appear in the same table. If they can be merged, try to merge them. If they cannot be merged, consider dividing the tables. For reasons, please consider optimizing the storage efficiency of the BLOB,TEXT columns in the InnoDB table.

After reading the above, do you have any further understanding of the difference between char, varchar and text in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.