The change of varchar type after 5.0.3 in MySQL

1.mysql varchar Type change:
Before MySQL 5.0.3:

0--255 bytes

The 20 in varchar (20) represents the number of bytes, and only 6 characters can be placed if the URF8 encoding is stored.

After MySQL 5.0.3:

0--65535 bytes

varchar (20) represents the number of characters, regardless of the encoding, both Chinese characters can also put 20. But up to 65532 bytes (two bytes of storage length, less than 255 bytes in 1 bytes to hold the length)

1. Storage Limits:

The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes representing the actual length (2 bytes in length over 255), so the maximum length cannot exceed 65532.

2. Length limit for character encoding:

If the character type is GBK, each character can be up to 2 bytes, and the maximum length cannot exceed 32766.

If the character type is UTF8, each character can be up to 3 bytes and the maximum length cannot exceed 21845.

If the limit above is defined, the varchar field is forcibly converted to the text type and generates warning.

3. Line length limit:

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires that a row's definition length cannot exceed 65535. If the defined table length exceeds this value. The prompt:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have the to change some columns to TEXT or BLOBs.

**************************
Through the Navicat test,

When the field is UTF8 encoded, the maximum length is set to 21845, but the warning error 1118 (42000): Row size too large ... is changed to a value slightly less than 21845.

Modified to GBK, with a maximum length of 32766, but warning error 1118 (42000): Row size too large ..., and a value slightly less than 32766 can be changed.

***************************

2.CHAR (M), VARCHAR (m) different

CHAR (M):

The length of the column is fixed, and the M value can be between 0~255

When you save a char value, a space is filled to the right of them to reach the specified length.

When a char value is retrieved, the trailing space is removed. No case conversions are made during the storage or retrieval process.

Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, take up 10 bytes of space, insufficient to automatically fill with spaces.

VARCHAR (M):

The length of the column is a variable length string, and the m value can be between 0~65535, (the maximum effective length of varchar is determined by the maximum row size and the character set used.) The overall maximum length is 65,532 bytes).

The varchar value is saved with only the required number of characters, plus one byte to record the length (two bytes if the column declaration is longer than 255).

The varchar value is not populated when it is saved. The trailing spaces on the value save and retrieve are still preserved, conforming to standard SQL.

VARCHAR stores variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

The biggest difference between char and varchar is that one is fixed length and one is variable length. Because it is variable length, it is actually stored as the actual string plus a byte of the length of the record string (if more than 255 requires two bytes). If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is clipped so that it fits. If the character being cropped is not a space, a warning is generated. If you crop a non-whitespace character, it causes an error (rather than a warning) and disables the insertion of values by using strict SQL mode.

3. The difference between varchar and text, blob types

The Varchar,blob and text types are variable-length types, and for their storage requirements depend on the actual length of the column values (denoted by L in the preceding table), rather than depending on the maximum possible size of the type. For example, a varchar (10) column can hold a string with a maximum length of 10 characters, the actual storage needs to be the length of the string, plus 1 bytes to record the length of the string. For the string ' ABCD ', L is 4 and the storage requirement is 5 bytes.

The blob and text types require either three-or 4-byte to record the length of the column values, depending on the maximum possible length of the type. varchar requires a defined size with a maximum limit of 65535 bytes, and the text is not required (without specifying a length). If you assign a value that exceeds the maximum length of a column type to a BLOB or text column, the value is truncated to fit.

A blob is a large binary object that can hold a variable amount of data. 4 blob types Tinyblob, blobs, Mediumblob, and Longblob differ only in the maximum length that they can hold values.

BLOBs can store pictures, text does not, text can only store plain text files. The 4 text types Tinytext, text, Mediumtext, and Longtext correspond to 4 blob types and have the same maximum length and storage requirements. The only difference between blob and text types is that the sorting and comparison of BLOB values is case-sensitive, and the text value is case insensitive. In other words, a text is a case-insensitive blob.

4. Summarize Char,varchar,text differences

The length difference,Char range is 0~255,varchar longest is 64k, but note here 64k is the length of the entire row, to consider the other column, and if there is not NULL when also occupy a bit, for different character sets , the effective length is not the same, for example UTF8, up to 21845, but also to remove the other column, but varchar in general the storage is sufficient. If you encounter a large text, consider using text, which can be up to 4G.

Efficiency is basically char>varchar>text, but if you are using a InnoDB engine, we recommend using varchar instead of char

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

It is necessary to choose the appropriate data type storage for the database, which has a certain effect on performance. Here in the piecemeal record two, for the int type, if you do not need to access negative values, preferably with unsigned; For fields that often appear in the Where statement, consider indexing, especially for indexing.

The change of varchar type after 5.0.3 in MySQL