char和varchar有哪些区别?varchar最大长度是多少?
- VARCHAR的定义
- VARCHAR的最大长度
- 最大行大小
- 可空列标识位
- 字符集的单字符最大字节数
- VARCHAR的长度标识位
- 样例
本文内容适用于MySQL 5.5/5.6/5.7/8.x
- VARCHAR(4),最多存储4个字符,有几个字符存储几个。存储字节数 = 数据值的字节和 + 1字节(长度标识,后面会讲到)
- CHAR(4),最多存储4个字符,不足4个尾部用空格填满。存储字节数 = 数据值的字节和 + 补位空格数
ERROR 1074 (42000): Column length too big for column ""long_char"" (max = 255); use BLOB or TEXT instead
- 行存储的最大字节数
- 数据之外的存储开销,官方定义中包括:NULL标识、长度标识
- 存储字符的字符集
mysql> create table test_varchar_length(v varchar(65536) not null);
ERROR 1074 (42000): Column length too big for column "v" (max = 65535); use BLOB or TEXT instead
/** 测试边界值65534,确认仍然过大;注意这里使用默认字符集latin1、单字节字符集 */
mysql> create table test_varchar_length(v varchar(65534) not null);
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
/** 测试边界值65533,创建成功,说明行最大值为65535 */
mysql> create table test_varchar_length(v varchar(65533) not null);
Query OK, 0 rows affected (0.02 sec)
/** 查看默认字符集,确认是latin1,每个字符只占用1个字节 */
mysql> show create table test_varchar_length;
+----------------------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+------------------------------------------------------------------------------------------------------------+
| test_varchar_length | CREATE TABLE `test_varchar_length` (
`v` varchar(65533) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
假设一张表中存在N个可空字段,NULL标识位需要⌈N / 8 ⌉ (向上取整)个字节。此时整行可用于数据存储的空间只有65535 − ⌈ N / 8 ⌉个字节。 Talk is cheep,一起来验证下: 在行大小的例子中,我们知道最大可创建65533字节长度的非空VARCHAR列。现在要创建一个可空列,每行需要1 bit的NULL标识位、MySQL会将其组装成1 byte的字段存放,那么我们应该可创建最大为65533(最大非空VARCHAR列) - 1(NULL标识列)= 65532字节的可空VARCHAR列:注意,这个标识位不是放在每列,而是每行共享。
/** 删除前面创建的表 */
mysql> drop table test_varchar_length;
Query OK, 0 rows affected (0.01 sec)
/** 测试边界值65533,确认仍然过大;注意这里使用默认字符集latin1、单字节字符集 */
mysql> create table test_varchar_length(v varchar(65533));
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
/** 测试边界值65532,创建成功,说明可空标识列确实占去了1字节;注意这里使用默认字符集latin1、单字节字符集 */
mysql> create table test_varchar_length(v varchar(65532));
Query OK, 0 rows affected (0.03 sec)
- GBK:单字符最大可占用2个字节。
- UTF8:单字符最大可占用3个字节。
- UTF8MB4:单字符最大占4个字节。
另外长度标志位是底层存储开销,不占用字段声明的字符长度。声明的字符长度的是数据的字符数,数据的字节数与字符集有关。但要注意,其计算根据的是字段声明的字符长度、计算可能的字节数,再决定长度标志的字节数。如VARCHAR(100),字符集为UTF8,可能的字节数为300,长度标识则为2字节。这是网上介绍错的最多的。
样例 公式应该都理解了:VARCHAR的最大长度 = (最大行大小 - NULL标识列占用字节数 - 长度标识字节数) / 字符集单字符最大字节数。有余数时向下取整。 接下来通过实验来验证。为了便于理解计算,例子做了一些调整:以VARCHAR(1)为例,可以存1个字符,MySQL会额外找一个字节存放长度标识
- 不设置可空列、这样可以去掉NULL标识列
- 为了便于体现长度标识位的差距,采用多个列的形式放大其存在
- 为了体现按可能字节数计算长度,这里采用多字节的字符集GBK
- 剩余空间为65535 - 255*2 = 65025字节
- 剩余空间可存放一个VARCHAR(32511) NOT NULL列(32511*2(GBK字符占2字节)+2(长度标识位占2字节)=65024)
mysql> drop table test_varchar_length;
Query OK, 0 rows affected (0.01 sec)
/** 测试边界值32512,确认仍然过大 */
mysql> create table test_varchar_length(v1 varchar(127) not null,v2 varchar(127) not null,vm varchar(32512) not null) CHARSET=GBK;
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
/** 测试边界值32511,创建成功,说明两个长度标识位共占去了2字节 */
mysql> create table test_varchar_length(v1 varchar(127) not null,v2 varchar(127) not null,vm varchar(32511) not null) CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)
- 剩余空间65535 - 258*2 = 65019字节
- 剩余空间可存放一个VARCHAR(32508) NOT NULL列(32508*2(GBK字符占2字节)+2(长度标识位占2字节)=65018):
mysql> drop table test_varchar_length;
Query OK, 0 rows affected (0.01 sec)
/** 测试边界值32509,确认仍然过大 */
mysql> create table test_varchar_length(v1 varchar(128) not null,v2 varchar(128) not null,vm varchar(32509) not null) CHARSET=GBK;
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
/** 测试边界值32508,创建成功,说明两个长度标识位共占去了4字节 */
mysql> create table test_varchar_length(v1 varchar(128) not null,v2 varchar(128) not null,vm varchar(32508) not null) CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)
- UTF8MB4字符中,中文字符需要3个字节(大部分中文只需要3字节,4字节主要是emoji等辅助平面字符),那么“中国cn”需要3+3+1+1共 8个字节
- VARCHAR(64) CHARSET utf8mb4字段,数据最大可能的字节数是64*4=256,所以需要 2个字节 作为长度标识位;
- 该字段是可以为空的,那么还需要NULL标识位,MySQL会生成一个 1字节 的NULL标识列来记录;
- 所以要存储“中国cn”,列需要8 + 2个字节,还需要1字节作为NULL标识列;因为该列是多个列共享的,如果该表只有一个字段,那么可以存储开销应该是11个字节,否则只能算作10.125字节(1/8等于0.125)