The difference between varchar and char in MySQL

The difference between varchar and char in MySQL (GO)

What is the maximum length of varchar in MySQL?

one. VarChar Storage rules:

4.0 versions, varchar (20), refers to a byte , if stored UTF8 kanji, can only save 6 (3 bytes per kanji)
More than 5.0 versions, varchar (20), refers to the number of characters , whether it is stored in numbers, letters or UTF8 Kanji (3 bytes per kanji), can be stored 20, the maximum size is 65532 bytes
The largest of the Mysql4 is only 20 bytes, but the MYSQL5 is different depending on the encoding and the storage size.

two. The difference between varchar and char:

Difference one, fixed length and variable length

Char indicates a fixed length, a fix, and a varchar expression that is variable in length. When the inserted string exceeds their length, it is handled as appropriate, and if it is in strict mode, the insert is rejected and the error message is denied, and if it is loose mode, it is intercepted and then inserted. If the length of the inserted string is less than the defined length, it is handled differently, such as char (10), which means that 10 characters are stored, no matter how much you insert, 10, or if less than 10, fill with a space. In the case of varchar (10), less than 10, the number of characters to be inserted will be saved.
How does varchar know the length of the stored string? In fact, for a varchar field, you would use one (if the string is less than 255) or two bytes (longer than 255) to store the length of the string. But because he needs to have a prefix to show how much he bytes (because varchar is getting longer, without this length value he doesn't know how to read the data).

Difference of two, storage capacity is different

For char, the maximum number of characters that can be stored is 255, regardless of the encoding.
and varchar, can hold up to 65,532 characters. 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

Use MySQL to determine if a rule is required to convert a data column type

1, in a data table, if the length of each data column is fixed, then the length of each data row will be fixed.

2, as long as the data table has a variable length of the data column, then the length of the data rows are variable.

3. If the length of the data row in a data table is variable, MySQL converts the data column of the fixed-length type in the table to the corresponding variable-length type in order to save storage space. Exception: A char data column that is less than 4 characters long is not converted to a varchar type

three. what is the maximum length of varchar in MySQL?

This is not a fixed number. Let's briefly explain the restriction rules.

1. Restriction rules

The restriction of a field has the following rules when the field is defined:

A) 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 65535 characters.

b) Encoding length limit

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.

c) Limit of the length of the president

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires a line definition length not to exceed 65535bytes. 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.

Determined by the character set, the character set is divided into single-byte and multi-byte
Latin1 one byte for up to 65,532 characters
GBK a character of two bytes and can save up to 32,766 characters
UTF8 a character of three bytes and can save up to 21,844 characters
Note that the length after char and varchar represents the number of characters, not the number of bytes.

2. Calculation Example

Give two examples to illustrate the actual length of the calculation.

A) If a table has only one varchar type, as defined as

CREATE table t4 (c varchar (N)) CHARSET=GBK;

The maximum value for n here is (65535-1-2)/2= 32,766 characters.

The reason for minus 1 is that the actual row storage starts with the second byte ';

The reason for minus 2 is that the 2 bytes of the varchar header represent the length;

The reason for the addition of 2 is that the character encoding is GBK.

b) If a table is defined as

CREATE table t4 (c int, C2 char (+), C3 varchar (N)) Charset=utf8;

The maximum value of n here is (65535-1-2-4-30*3)/3=21812

Minus 1 and minus 2 are the same as in the previous example;

The reason for minus 4 is that the int type C accounts for 4 bytes;

The reason for reducing 30*3 is that char (30) occupies 90 bytes and the encoding is UTF8.

If the varchar exceeds the B rule above, and is strongly turned into the text type, then each field occupies a defined length of 11 bytes, which is not "varchar", of course.

The maximum value of n here is (65535-1-2-4-30*3)/3=21812

CREATE table t4 (c int, C2 char (+), C3 varchar (21812)) Engine=innodb DEFAULT Charset=utf8

The difference between varchar and char in MySQL