UTF8字符集下：
?
| 1234567891011121314151617181920212223 | 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 1SQL>insert into test values(null,'一二三四五六七八九十1');ERROR 1406 (22001): Data too long for column 'name' at row 1SQL>insert into test values(null,'一二三四五六七八九十一');ERROR 1406 (22001): Data too long for column 'name' at row 1SQL>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字符集下：
?
| 1234567891011121314151617181920212223 | 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 1SQL>insert into test values(null,'一二三四五六七八九十1');ERROR 1406 (22001): Data too long for column 'name' at row 1SQL>insert into test values(null,'一二三四五六七八九十一');ERROR 1406 (22001): Data too long for column 'name' at row 1SQL>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。
?
| 12345678910111213141516171819202122232425 | 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 1SQL>insert into test values(null,'一二三四五1');ERROR 1406 (22001): Data too long for column 'name' at row 1SQL>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) |
點(diǎn)擊查看更多內(nèi)容
為 TA 點(diǎn)贊
評論
評論
共同學(xué)習(xí)，寫下你的評論
評論加載中...
作者其他優(yōu)質(zhì)文章
正在加載中
感謝您的支持，我會繼續(xù)努力的～
掃碼打賞，你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃，即可進(jìn)行掃碼打賞哦