Explore the definition length of varchar in Mysql _mysql

UTF8 Character Set:

Sql>create table test (id int auto_increment,name varchar (), primary key (ID));
Sql>insert into test values (null, ' 1234567890 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' 1,234,567,890 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' Abcdefghig ');
Query OK, 1 row affected (0.01 sec) Sql>insert into test values (null,12345678901);
ERROR 1406 (22001): Data too long for column ' name ' in row 1 Sql>insert into test values (null, ' 1,234,567,891 ');
ERROR 1406 (22001): Data too long for column ' name ' in row 1 Sql>insert into test values (null, ' 1,234,567,891 '); ERROR 1406 (22001): Data too long for column ' name ' at row 1 sql>select id,name,length (name), Char_length (name) from TES

T +----+--------------------------------+--------------+-------------------+
 | ID | name | Length (name) |
 Char_length (name) | +----+--------------------------------+--------------+-------------------+
 | 1 | 1234567890 | 10 |
 10 | | 2 | 1,234,567,890 | 30|
 10 | | 3 | Abcdefghig | 10 |
 10 |

 +----+--------------------------------+--------------+-------------------+ 3 rows in Set (0.00 sec)

   GBK Character set:

Sql>create table test (id int auto_increment,name varchar (), primary key (ID));
Sql>insert into test values (null, ' 1234567890 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' 1,234,567,890 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' Abcdefghig ');
Query OK, 1 row affected (0.01 sec) Sql>insert into test values (null,12345678901);
ERROR 1406 (22001): Data too long for column ' name ' in row 1 Sql>insert into test values (null, ' 1,234,567,891 ');
ERROR 1406 (22001): Data too long for column ' name ' in row 1 Sql>insert into test values (null, ' 1,234,567,891 '); ERROR 1406 (22001): Data too long for column ' name ' at row 1 sql>select id,name,length (name), Char_length (name) from TES

T +----+----------------------+--------------+-------------------+
 | ID | name | Length (name) |
 Char_length (name) | +----+----------------------+--------------+-------------------+
 | 1 | 1234567890 | 10 |
 10 | | 2 | 1,234,567,890 | 20 |
 10 | | 3 | ABCdefGhig | 10 |
 10 |

 +----+----------------------+--------------+-------------------+ 3 rows in Set (0.00 sec)

Thus, varchar defines the length of the unit as a character, even if the 1 multibyte characters are 1 characters, such as Chinese and English letters are treated as 1 characters.

So what is the maximum length that varchar can define? This is related to the character set you are currently using. The maximum length is 65535 bytes (this is the maximum row size, shared by all columns), and the maximum length that can be defined under different character sets will vary, such as UTF8 under 21845. It is said that the length of the varchar in the MySQL5 is also a character, while the MySQL4 in the byte, unconfirmed, interested in the environment can be measured by themselves.

By the way, the char data type defines a character with a maximum length of 255.

Sql>create table test (id int auto_increment,name char (5), primary key (ID));
Query OK, 0 rows affected (0.09 sec) Sql>insert into test values (NULL, ' 123 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' 12345 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (NULL, ' 123 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null, ' 12345 ');
Query OK, 1 row Affected (0.00 sec) Sql>insert into test values (null,123456);
ERROR 1406 (22001): Data too long for column ' name ' in row 1 Sql>insert into test values (null, ' 123,451 '); ERROR 1406 (22001): Data too long for column ' name ' at row 1 sql>select id,name,length (name), Char_length (name) from TES

T +----+-----------------+--------------+-------------------+
 | ID | name | Length (name) |
 Char_length (name) | +----+-----------------+--------------+-------------------+
 | 1 | 123 | 3 |
 3 | | 2 | 12345 | 5 |
 5 | | 3 | 123 | 9 |
 3 | | 4 | 12345 | 15 |
 5 | +----+-----------------+--------------+-------------------+ 4 rows in Set (0.00 sec)