探討MySQL中varchar的界說長度。本站提示廣大學習愛好者：（探討MySQL中varchar的界說長度）文章只能為提供參考，不一定能成為您想要的結果。以下是探討MySQL中varchar的界說長度正文
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)
