Shulou(Shulou.com)06/01 Report--

The length of char is fixed, no matter how much data is stored, the upper limit is 255bytes, and the upper limit of varchar is 65535 bytes.

Char (4); / / this 4 means to store up to 4 [characters]

The length can be any value from 0 to 255, when the stored string length is less than the specified length

The space on the right side of the string will be filled in to the specified length, and the right space will be deleted when querying the string.

Second, varchar is longer.

Varchar (4) / / this 4 means to store up to 4 [characters]

Length can be specified as a value between 0 and 65535 (the maximum valid length of a VARCHAR is determined by the maximum line size and the character set used. The overall maximum length is 65532 bytes)

When the VARCHAR value is saved, only the number of characters required is saved, plus one byte to record the length (if the length of the column declaration exceeds 255, two bytes are used)

Varchar (30) stores up to 30 characters; varchar (5) and (200) store the same amount of space as hello, but the latter consumes more memory when sorting because order by col uses fixed_length to calculate col length (the same is true of memory engines)

The meaning of 20 in int (20) refers to the length of the display character.

But to add parameters, the maximum is 255. for example, it is the id for recording the number of rows. If you insert 10 pieces of data, it will display 00000000001 ~ 00000000010. When the number of characters exceeds 11, it will only show 11 digits. If you do not add the parameter that makes it preceded by 0 before 11 digits, it will not be preceded by 0.

20 indicates that the maximum display width is 20, but it still accounts for 4 bytes of storage, and the storage range remains the same.

IV. Text

The upper limit of text is 65535, and the default value cannot be set.

5. Summarize the differences between char,varchar,text

Char, fixed length, high speed, the possibility of space waste, will deal with trailing spaces

Varchar, long memory, slow speed, no space waste, no trailing space, upper limit of 65535, but the actual storage length is 65532 maximum available

Text, long memory big data, slow speed, no space waste, no trailing space, upper limit of 65535, will use extra space to store data length, Gu can all use 65535.

According to the difference in length, the maximum length of char is 64k, but note that 64k here is the length of the entire row, taking into account other column, and it will occupy a bit if there is not null. The effective length varies for different character sets, such as utf8, up to 21845, and other column should be removed, but varchar is generally sufficient for storage. If you encounter large text, consider using text, up to 4G.

In terms of efficiency, it is basically char > varchar > text, but if you are using Innodb engine, it is recommended to use varchar instead of char

Char and varchar can have default values, while text cannot specify default values

When fetching data, char types need to remove extra spaces with trim (), while varchar is not needed. Nevertheless, the access number of char is much faster than that of varchar, because its length is fixed and it is convenient for programs to store and find. But char also pays the price of space, because its length is fixed, so it is inevitable that extra space placeholders occupy space. It can be said that space is exchanged for time efficiency, while varchar takes space efficiency as the first place. In addition, char is stored by occupying 1 byte for English characters (ASCII) and two bytes for a Chinese character.

The storage mode of varchar is to occupy 2 bytes for each English character and 2 bytes for Chinese characters, both of which are non-unicode character data.

In terms of space:

We can see from the official documents that when varchar is greater than a certain value, it will be automatically converted to text. The general rules are as follows:

Greater than varchar (255) becomes tinytext

Greater than varchar (500) becomes text

Greater than varchar (20000) becomes mediumtext

So there is not much difference between using varchar and text for too large content.

Performance:

Indexes are the most critical factor affecting performance, while for text, only prefix indexes can be added, and prefix indexes can only reach a maximum of 1000 bytes.

It seems that varhcar can add all the indexes, but after testing, it is not. Because of the internal conversion, long varchar can only add an index of 1000 bytes, which will be truncated automatically if it is too long.

When the length exceeds 255, there is no essential difference between using varchar and text, you only need to consider the two types of features.

(4) Why is mysql so designed

It doesn't make sense for most applications, just specify the number of characters that some tools use to display characters; int (1) and int (20) store and calculate the same.

Reference: http://www.cnblogs.com/billyxp/p/3548540.html

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.