UTF8字符集下:
SQL>create table test(id int auto_increment,name varchar(10),primary key(id)); SQL>insert into test values(null,'1234567890'); Query OK, 1 row affected (0.00 sec) SQL>insert into test values(null,'一二三四五六七八九十'); 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' at row 1 SQL>insert into test values(null,'一二三四五六七八九十1'); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>insert into test values(null,'一二三四五六七八九十一'); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>select id,name,length(name),char_length(name) from test; +----+--------------------------------+--------------+-------------------+ | id | name | length(name) | char_length(name) | +----+--------------------------------+--------------+-------------------+ | 1 | 1234567890 | 10 | 10 | | 2 | 一二三四五六七八九十 | 30 | 10 | | 3 | abcdefghig | 10 | 10 | +----+--------------------------------+--------------+-------------------+ 3 rows in set (0.00 sec)
GBK字符集下:
SQL>create table test(id int auto_increment,name varchar(10),primary key(id)); SQL>insert into test values(null,'1234567890'); Query OK, 1 row affected (0.00 sec) SQL>insert into test values(null,'一二三四五六七八九十'); 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' at row 1 SQL>insert into test values(null,'一二三四五六七八九十1'); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>insert into test values(null,'一二三四五六七八九十一'); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>select id,name,length(name),char_length(name) from test; +----+----------------------+--------------+-------------------+ | id | name | length(name) | char_length(name) | +----+----------------------+--------------+-------------------+ | 1 | 1234567890 | 10 | 10 | | 2 | 一二三四五六七八九十 | 20 | 10 | | 3 | abcdefghig | 10 | 10 | +----+----------------------+--------------+-------------------+ 3 rows in set (0.00 sec)
由此可見,varchar定義的長度的單位是字符,哪怕是1個多字節字符也是1個字符,如中文和英文字母都被當作1個字符來對待。
那麼varchar能夠定義的最大長度是多少呢?這個和你當前所使用的字符集有關。拋開字符,其最大長度為65535字節(這是最大行大小,由所有列共享),而放在不同的字符集下,能夠定義的最大長度就會有所不同,如UTF8下是21845。據說MySQL5中varchar的長度也為字符,而MySQL4中的則為字節,未經證實,感興趣的有環境可以自己測下。
順便補充一下,char數據類型定義的長度也為字符,其最大長度為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,'一二三'); Query OK, 1 row affected (0.00 sec) SQL>insert into test values(null,'一二三四五'); Query OK, 1 row affected (0.00 sec) SQL>insert into test values(null,123456); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>insert into test values(null,'一二三四五1'); ERROR 1406 (22001): Data too long for column 'name' at row 1 SQL>select id,name,length(name),char_length(name) from test; +----+-----------------+--------------+-------------------+ | id | name | length(name) | char_length(name) | +----+-----------------+--------------+-------------------+ | 1 | 123 | 3 | 3 | | 2 | 12345 | 5 | 5 | | 3 | 一二三 | 9 | 3 | | 4 | 一二三四五 | 15 | 5 | +----+-----------------+--------------+-------------------+ 4 rows in set (0.00 sec)