设为首页收藏本站
查看: 87|回复: 0

[PHP] 探究MySQL中varchar的定义长度_MySQL-mysql教程

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    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)
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表