如果要了解MySQL其他类型长度,可以参考《MySQL字段长度、取值范围、存储开销》
以我多年经验来看,VARCHAR的最大长度、字符串类型选择,用MySQL的人中十之七八是不清楚的。网上文章鱼目混珠,以讹传讹居多。
本文不止介绍了原理,还提供了案例手把手教你自己分析,彻底解决你的疑惑。
假设有个VARCHAR(64) CHARSET utf8mb4
列,存储了中国cn
这个字符串。
那你猜一猜,MySQL存储时用了多少字节?
- A:4 Bytes
- B:5 Bytes
- C:8 Bytes
- D:9 Bytes
- E:10 Bytes
- F:10.125 Bytes
- G:11 Bytes
- H:12 Bytes
- I:12.125 Bytes
- K:13 Bytes
正确答案是F和G。
如果您没猜对,那么花7~10分钟读完本文,即可破解这一谜题。成长快乐轻轻松松。
本文内容适用于MySQL 5.5/5.6/5.7/8.x
VARCHAR
的定义
VARCHAR
是变长字符串。
考虑其变长原理中有较多要素,在具体分解前,有必要一起重温下官方定义。
为了便于理解,我用CHAR
定长类型来对比介绍。先看两个小例子:
VARCHAR(4)
,最多存储4个字符
,有几个字符存储几个。存储字节数 = 数据值的字节和 + 1字节(长度标识,后面会讲到)CHAR(4)
,最多存储4个字符
,不足4个尾部用空格填满。存储字节数 = 数据值的字节和 + 补位空格数
概括地说,VARCHAR
和CHAR
都是MySQL的字符串类型,存储多个字符、可设置最大存储的字符数,存储开销都与数据长度、字符集有关。是MySQL最常用的字符串类型。
CHAR
和VARCHAR
具体对比:
特性 | CHAR |
VARCHAR |
---|---|---|
长度 | 定长,固定字符数 最大255个字符 数据长度不足声明值时,在尾部自动填充空格 |
长度可变,可设置最大存储字符数 最大不超过行大小(默认65535字节,注意是字节,下面会讲原因) |
前缀 | 无 | 1~2字节,看列长度是否可能超过255字节 比如 VARCHAR(100) ,字符集为UTF8 ,则字节最大可能为300字节,所以会使用2个字节标识长度 |
有否尾部空格 | 长度不足默认用空格填满 检索和获取时会自动去除 |
不会自动填充空格输入值就包含空格,则会存储,检索和获取数据都会体现 |
超长处理 | 超长部分如果是空格自动截断 如果是字符,严格模式下会报错 |
超长部分如果是空格自动截断,并生成警告 如果是字符,严格模式下会报错 |
存储开销 | 数据值的字节和 + 补位空格数 | 数据值的字节和 + 长度标识字节数 |
- 如果开启
PAD_CHAR_TO_FULL_LENGTH
模式,检索时尾部空格不会去除 CHAR
超过255字符会报错,提示使用TEXT
或BLOB
:ERROR 1074 (42000): Column length too big for column ''long_char'' (max = 255); use BLOB or TEXT instead
这是两个类型的简单介绍。要了解MySQL类型详细内容,可参考《MySQL字段长度、取值范围、存储开销》。
VARCHAR
的最大长度
在MySQL官方定义中,常用的COMPACT
、DYNAMIC
行模式下,最大长度受几个因素影响:
- 行存储的最大字节数
- 数据之外的存储开销,官方定义中包括:
NULL标识
、长度标识
- 存储字符的字符集
算法如下:
最大长度(字符数) = (行存储最大字节数
- NULL标识列占用字节数
- 长度标识字节数
) / 字符集单字符最大字节数
。有余数时向下取整。
关于MySQL行大小定义,可参考《MySQL字段长度、取值范围、存储开销》
下面通过逐步实例验证,演示如何计算出最大长度。
最大行大小
MySQL行默认最大65535
字节,是所有列共享的,所以VARCHAR
的最大值受此限制。
接下来,我们要创建一个65536
字节的VARCHAR
,来验证这个边界值。
前面讲过,
VARCHAR
声明的长度是指字符数。要换算为65536
字节,最好一个字符只占一个字节。
所以这里使用了latin1
字符集(MySQL默认字符集,不指定即为默认)。
要了解更多MySQL字符集,可参考《MySQL有哪些字符集?如何使用?》
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
可以看到报错了,提示我们行最大长度为65535
字节。
如果我们要插入一个非空的VARCHAR
,其最大长度不能超过65535(行最大值) - 2(长度标识位) = 65533
字节(长度标识位需2字节才能表示216=65536个数字):
/** 测试边界值65534,确认仍然过大;注意这里使用默认字符集latin1、单字节字符集 */
mysql>