【MySQL】不同字符集varchar长度问题
一般我们的理解，如Oracle，如果字符集是GBK，那么每个中文字符占2个字节，如果是UTF8，那么每个中文字符占3个字节。
1：Oracle 11.2.0.4 for Windows
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK SQL> create table t(id number(5),name varchar2(6)); 表已创建。 SQL> insert into t values(1,'张三丰'); 已创建 1 行。 SQL> insert into t values(1,'张三丰a'); insert into t values(1,'张三丰a') * 第 1 行出现错误: ORA-12899: 列 "SCOTT"."T"."NAME" 的值太大 (实际值: 7, 最大值: 6) SQL> |
在MySQL中会怎样呢？当然MySQL5.0前后是不同的，当然现在我们用的大多是5.0以后的，所以，我们就不考虑之前的情况了
2：MySQL 5.7 创建两个数据库db1,db2，字符集分别是UTF8,GBK
|
1 2 3 4 5 6 7 |
mysql> create database db1 default charset utf8; Query OK, 1 row affected (0.01 sec) mysql> create database db2 default charset gbk; Query OK, 1 row affected (0.02 sec) mysql> |
2.1：db1：UTF8字符集
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
mysql> use db1 Database changed mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> ## 创建t1表，一个字段，长度是65535，提示name字段最长是21845 ## 因为db1是utf8字符集，所以，最大宽度是65535/3 mysql> create table t1(name varchar(65535)); ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead mysql> ## 将name长度改为21845，提示太长了，原因是表的元数据还需要存储空间 mysql> create table t1(name varchar(21845)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> ## 再改小点儿，够用了 mysql> create table t1(name varchar(21844)); Query OK, 0 rows affected (0.02 sec) mysql> ## 如果再加一个字段设置为varchar(1)，效果如下。 ## 因为表字段最大长度是21844，此时加一个字段，长度是1，所以，21844要减1, mysql> create table t2(id varchar(1),name varchar(21844)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> ## 当然了，数据库的存储还是比较严禁的，-1后只是长度满足了最长的需求，但存储上还是不够的，所以得减点儿才行 mysql> create table t2(id varchar(1),name varchar(21843)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> mysql> create table t2(id varchar(1),name varchar(21842)); Query OK, 0 rows affected (0.02 sec) mysql> |
2.2：db2：GBK字符集
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
## 这里就不细说了，因为字符集是GBK，所以65535/2 mysql> use db2 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | gbk | +------------------------+-------+ 1 row in set (0.00 sec) mysql> mysql> create table t1(name varchar(65535)); ERROR 1074 (42000): Column length too big for column 'name' (max = 32767); use BLOB or TEXT instead mysql> mysql> create table t1(name varchar(32767)); ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs mysql> mysql> create table t1(name varchar(32766)); Query OK, 0 rows affected (0.01 sec) mysql> |
3：MySQL插入数据
表中的name长度为9，因为在建表的时候已经按照字符集做了响应的缩减（本测试是UTF8，所以，一个字符占三个字节），所以字段长度的9，就是可以存放9个字符（中文或英文）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> use db1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> create table t(id int(5) auto_increment,name varchar(9),primary key(id)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> insert into t (name) values('张三丰名君宝字符元'); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from t; +----+-----------------------------+ | id | name | +----+-----------------------------+ | 1 | 张三丰名君宝字符元 | +----+-----------------------------+ 1 row in set (0.00 sec) mysql> insert into t (name) values('123456789'); Query OK, 1 row affected (0.01 sec) mysql> insert into t (name) values('1234567890'); ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> |
4：小结
1）不同的字符集，varchar的最大长度也不同。gbk：32767 utf8：21845
2）表中所有字段是有长度限制的，根据字符集不同而不同（gbk：32767 utf8：21845），所以，我们在建表的时候，字符型字段的长度也要搂着用，别动不动就几百几千的设置，一是给别的字段留点儿空间，二是，如果varchar过长，导致这个字段的索引会占用更大的空间和使用更多的内存，对性能造成一定的影响。