The length of varchar in MySQL is described in detail

In the version above MySQL5.0, the length of the varchar data type is supported to 65535, which means that 65,532 bytes of data can be stored, and the start and end bits take up 3 bytes, that is, data stored in a fixed text or blob format in 4.1 or below can be used with variable length varchar to store, this will effectively reduce the size of the database file.

I. VARCHAR storage and row length limits

In 1.VARCHAR (n), n refers to the length of the character, the VARCHAR type has a maximum support of 65535, refers to 65,535 bytes, but does not support 65535-length VARCHAR, The 65535 should contain the length of all fields, the length of the variable long field, and the accumulation of the null indicator bit. Where the content begins with 1 to 2 bytes to represent the actual length (2 bytes in length over 255). So there are other expenses, actually can store the length of 65532.

(the varchar type of MySQL database has a maximum length limit of 255 in the version below 5.0.3, and its data range can be 0~255)

2. Because the null designator occupies a byte, you can remove the NOT null restriction.

3.MySQL requires a row to define a length of not more than 65535 is the total length of all columns can not exceed 65535. If the total length of the column exceeds this length, it still cannot be created.

Two Coding limits for varchar length

1. Overview

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

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

If the definition exceeds the above limit, the varchar field is forcibly converted to the text type and produces warning.

2. For example

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

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

Then the maximum value for n here is (65535-1-2)/2= 32766.

The reason for minus 1 is that the actual row is stored starting with the second byte ';

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

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

(2) If a table is defined as

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

Then the maximum value for n here is (65535-1-2-4-30*3)/3=21812

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

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

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

If the varchar exceeds the above B rule and is strongly converted to the text type, then each field occupies a defined length of 11 bytes, of course this is not "varchar" anymore.

Three About SQL mode

In MySQL, SQL schemas are commonly used to address the following categories of issues:

1. By setting up SQL Mode, the data can be verified with different strict degree, and the data accuracy is ensured effectively.

2. By setting SQL mode to ANSI, which guarantees that most SQL conforms to the standard SQL syntax, there is no need to make larger changes to the business SQL when the application is migrated between different databases.

3. Before data migration between different databases, you can make the data on MySQL easier to migrate to the target database by setting SQL mode.

View when SQL mode

Mysql>select @ @sql_mode;

Strict_trans_tables (Strict mode) to achieve strict data validation, so that error data can not be inserted into the table.

If you set Sql_mode to ', you may be able to create a table, but there will be a warning message

Mysql>set session sql_mode= ';

Mysql>create table Test4 (a varchar (25000)) Charset=utf8;

Mysql>show warnings;

Warning messages are prompted and can be created because MySQL automatically converts varchar to the text type.

Mysql>show CREATE TABLE test4;

1. Restrict the Rules

The limits of a field have the following rules when the field is defined:

A) storage limits

varchar field is to store the actual content of 0 butyl outside the clustered index, the content open first with 1 to 2 bytes implied actual length (the length spans 255 when the need for 2 bytes), is the maximum length cannot cross 65535.

b Coding Length Limit

If the character type is GBK, each character is up to 2 bytes and the maximum length cannot span 32766;

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

For the English-speaking forum, the application of GBK for each character occupies 2 bytes, while the application of UTF-8 English is only one byte.

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

c) The length of the limit

The length of a row definition is the limit of the varchar length in the actual application. MySQL request a line definition length cannot span 65535. If you define a table length that spans this value, you are prompted

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

If a varchar is set in an item (50)
Well, that's 50 for English.
So what about Chinese?
Utf-8 Chinese occupies 3 bytes
So, is this varchar (50) Only save 16 characters?
MySQL varchar (50) in both Chinese and English, there are 50.

MySQL5 document that describes the varchar field type: varchar (m) variable length string. M represents the maximum column length. The range of M is 0 to 65,535. (The maximum actual length of the varchar is determined by the longest row size and the character set used, with a maximum effective length of 65,532 bytes).
Why does it change so? It really feels like the MySQL manual is too unfriendly, because you have to read it carefully before you find this description: MySQL 5.1 complies with the standard SQL specification and does not remove trailing spaces from the varchar value. varchar the prefix + data with a byte or two byte long when saving. If the length of the varchar column declaration is greater than 255, the length prefix is two bytes.
Well, it seems to understand a little. But he said the length of more than 255 when the use of 2 byte length prefix, primary subtraction: 65535-2 = 65533 AH. I do not know how to calculate these Daniel, leave aside the question?
Note: I tested the use of UTF8 encoding, the maximum length of varchar is 21854 bytes.
In MySQL version 5.0.45, UTF8 is tested under the database encoding: varchar is defined as the maximum length of 21785. In other words, no matter the letter, the number, the Chinese character, can only put 21,785.
Supposition: The varchar byte maximum 65535,utf8 encodes a character 3 byte 65535/3=21785.