MySQL的varchar定义长度到底是字节还是字符

环境:CentOS5.x,MySQL5.1.x
UTF8字符集下:

[java]
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;

[/java]


[java]
+—-+——————————–+————–+——————-+
| 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)

[/java]

GBK字符集下:

[java]
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;

[/java]

 

[java]
+—-+———————-+————–+——————-+
| 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)

[/java]

由此可见,varchar定义的长度的单位是字符,哪怕是1个多字节字符也是1个字符,如中文和英文字母都被当作1个字符来对待。
那么varchar能够定义的最大长度是多少呢?这个和你当前所使用的字符集有关。抛开字符,其最大长度为65535字节(这是最大行大小,由所有列共享),而放在不同的字符集下,能够定义的最大长度就会有所不同,如UTF8下是21845。据说MySQL5中varchar的长度也为字符,而MySQL4中的则为字节,未经证实,感兴趣的有环境可以自己测下。
顺便补充一下,char数据类型定义的长度也为字符,其最大长度为255。

[java]
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;

[/java]

 

[java]
+—-+—————–+————–+——————-+
| 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)

[/java]

(转载本站文章请注明作者和出处 JavaRanger – javaranger.com ,请勿用于任何商业用途)

本文链接: http://www.javaranger.com/archives/763