MySQL的varchar定义长度到底是字节还是字符 叁歲伎倆 2021-06-24 14:34 502阅读 0赞 相信这个问题也会困扰不少人,尤其是使用过其它数据库(如Oracle)的人,之前我也没有太在意这个问题,再加上一些书籍和网上的文章讲的不够细致,又没测试过,导致我一直理解错误。下面通过实例来解释,在开始之前先简单介绍下字符和字节的区别。 字符 人们使用的记号,抽象意义上的一个符号。一个汉字和英文就是一个字符,如'1', '中', 'a', '$', '¥',…… 字节 计算机中存储数据的单元,一个8位的二进制数,是一个很具体的存储空间,如0x01, 0x45, 0xFA,…… 详细的可以参考[http://www.regexlab.com/zh/encoding.htm][http_www.regexlab.com_zh_encoding.htm],正确理解相关概念还是很重要的。 好了,如果你已经理解了字符和字节的区别,下面就容易多了。 环境:CentOS5.x,MySQL5.1.x 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) [http_www.regexlab.com_zh_encoding.htm]: http://www.regexlab.com/zh/encoding.htm
还没有评论,来说两句吧...