MYSQL教程探究MySQL中varchar的定义长度

《MYSQL教程探究MySQL中varchar的定义长度》要点：
本文介绍了MYSQL教程探究MySQL中varchar的定义长度，希望对您有用。如果有疑问，可以联系我们。

MYSQL学习UTF8字符集下：

MYSQL学习
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,'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)

MYSQL学习?? GBK字符集下：

MYSQL学习
SQL>create table test(id int auto_increment,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)

MYSQL学习?? 由此可见,varchar定义的长度的单位是字符,哪怕是1个多字节字符也是1个字符,如中文和英文字母都被当作1个字符来对待.

MYSQL学习?? 那么varchar能够定义的最大长度是多少呢?这个和你当前所使用的字符集有关.抛开字符,其最大长度为65535字节(这是最大行大小,由所有列共享),而放在不同的字符集下,能够定义的最大长度就会有所不同,如UTF8下是21845.据说MySQL5中varchar的长度也为字符,而MySQL4中的则为字节,未经证实,感兴趣的有环境可以自己测下.

MYSQL学习?? 顺便补充一下,char数据类型定义的长度也为字符,其最大长度为255.

MYSQL学习
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,'12345');
Query OK,'一二三');
Query OK,'一二三四五');
Query OK,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,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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!