MySQL 中 VARCHAR 最大长度及 CHAR 和 VARCHAR 的区别

问题

创建一个9个字段的表,所有的字段都为VARCHAR类型,其中前8个字段都是最大能放下7279个字符,允许为空,字符集类型为latin1,即一个字符只占一个字节,建表语句如下:

CREATE TABLE test_varchar_length6(
    a VARCHAR(7279),
    b VARCHAR(7279),
    c VARCHAR(7279),
    d VARCHAR(7279),
    e VARCHAR(7279),
    f VARCHAR(7279),
    g VARCHAR(7279),
    h VARCHAR(7279),
    i VARCHAR(?)
)DEFAULT CHARSET=latin1

那么i的最大字符数为多少?问题最后揭晓。

VARCHAR 定义

VARCHAR是变长字符串,便于理解,使用CHAR定长类型来对比介绍。

  • VARCAHR,最多存储4个字符,有几个字符存储几个。存储字节数=数据值的字节和+1字节(长度标识,后面会讲到)
  • CHAR(4),最多存储4个字符,不足4个尾部用空格填满。存储字节数=数据值的字节和+补位空格数

概括地说,VARCHARCHAR都是 MySQL 的字符类型,存储多个字符、可设置最大存储的字符数,存储开销与数据长度、字符集有关。是MySQL 最常用的字符串类型。
CHARVARCHAR具体对比:

特性 CHAR VARCHAR
长度 定长,固定字符数最大255个字符数据长度不足声明值时,在尾部自动填充空格 长度可变,可设置最大存储字符数最大不超过行大小(默认65535字节,注意是字节,下面会讲原因)
前缀 1~2字节,看列长度是否可能超过255字节比如VARCHAR(100),字符集为UTF8,则字节最大可能为300字节,所以会使用2个字节标识长度
有否尾部空格 长度不足默认用空格填满检索和获取时会自动去除 不会自动填充空格输入值就包含空格,则会存储,检索和获取数据都会体现
超长处理 超长部分如果是空格自动截断如果是字符,严格模式下会报错 超长部分如果是空格自动截断,并生成警告如果是字符,严格模式下会报错
存储开销 数据值的字节和 + 补位空格数 数据值的字节和 + 长度标识字节数
  • 如果开启PAD_CHAR_TO_FULL_LENGTH模式,检索时尾部空格不会去除
  • CHAR超过255字符会报错,提示使用TEXTBLOB
ERROR 1074 (42000): Column length too big for column ''long_char''     (max = 255); use BLOB or TEXT instead

VARCHAR 的最大长度

在MySQL官方定义中,常用的 COMPACT、DYNAMIC行 模式下,最大长度受几个因素影响:

  • 行存储的最大字节数
  • 数据之外的存储开销,官方定义中包括:NULL标识长度标识
  • 存储字符的字符集

最大长度(字符数) = (行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数) / 字符集单字符最大字节数。有余数时向下取整。

最大行大小

MySQL 行默认最大 65535 字节,是所有列共享的,所以 VARCHAR 的最大值受此限制。
下面我们创建一个 65535 字节的 VARCHAR,来验证这个边界值。

前面讲过,VARCHAR声明的长度是指字符数。要换算为65536字节,最好一个字符只占一个字节。这里测试使用的是MySQL 8.0DEFAULT CHARSET=utf8mb4,所以这里使用了latin1字符集。
CREATE TABLE test_varchar_length(
    `v` VARCHAR(65536) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

> 1074 - Column length too big for column 'v' (max = 65535); use BLOB or TEXT instead

可以看到报错了,提示我们行最大长度为 65535 字节。
如果我们要插入一个非空的 VARCHAR,其最大长度不能超过 65535(行最大值)-2(长度标识位)=65533字节(长度标识位需两字节才能标识216=65535个数字):

-- 测试边界值65535,确认仍然过大;注意这里使用字符集latin1,单字节字符集
CREATE TABLE test_varchar_length(
    v VARCHAR(65534) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

> 1118 - 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
CREATE TABLE test_varchar_length(
    v VARCHAR(65533) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=latin1

> OK

-- 查看字符集,是latin1,每个字符只占用1个字节
SHOW CREATE TABLE test_varchar_length;

可控列标识位

COMPACTDYNAMIC行格式下,行大小除了数据列长度,还包括可空列标识,即NULL标识位。

  • 如果有一个列允许为空,则需要1 bit来标识,每8 bits的标识会组成一个字段,该字段会存放在每行最开始的位置。

    注意这个标识位不是放在每列,而是每行共享。

  • 假设一张表中存在N个可空字段,NULL标识位需要[N/8](向上取整)个字节。此时整行可用于数据存储的空间只有65535-[N/8]个字节。

下面通过实例来验证一下:
在行大小的例子中,我们知道最大可创建 65535 字节长度的非空 VARCHAR列。现在要创建一个可空列,每行需要1 bit的 NULL 标识位、MySQL会将其组装成 1 byte的字段存放,那么我们应该创建最大为65533(最大非空VARCHAR列)-1(NULL标识列)=65532字节的可空VARCHAR列:

-- 测试边界值65533,确认仍然过大;注意这里使用默认字符集latin1、单字节字符集
CREATE TABLE test_varchar_length3(
    v VARCHAR(65533)
)ENGINE=INNODB DEFAULT CHARSET=latin1

> 1118 - 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、单字节字符集
CREATE TABLE test_varchar_length3(
    v VARCHAR(65532)
)ENGINE=INNODB DEFAULT CHARSET=latin1

> OK

计算VARCHAR的最大长度,可空标识位是最容易忽略的。

字符集的单字节符最大字节数

字符集单字符最大字节数不难理解,列举MySQL常见的三个字符集:

  • GBK:单字节最大可占用2个字节。
  • UTF8:单字节最大可占用3个字节。
  • UTF8MB4:单字节最大占4个字节。
    假设还有6字节可以存放字符,按字符占用最大字节数来算,可以存放3个GBK、2个UTF8、1个UTF8MB4

VARCHAR 的长度标识位

长度标识位是相对比较复杂的,网上的介绍错的很多,也容易算错。
其作用是记录数据的 字节数。

存储开销是小于255只要1字节、大于255后使用两字节。是因为按照可能的数据大小,分为0 ~ 255(2<sup>8</sup>)256 ~ 65535(2<sup>16</sup>),刚好对应1字节和2字节。>16),刚好对应1字节和2字节。

但要注意,其计算根据的是字段声明的字符长度、计算可能的字节数,再决定长度标志的字节数。如`VARCHAR(100)`,字符集为`UTF8`,可能的字节数为300,长度标识则为2字节。这是网上介绍错的最多的。

另外长度标志位只是存储开销,不影响占用声明的字符长度。声明的字符长度的是数据的字符数,允许的最大字符数与字符集有关。

以VARCHAR(1)为例,可以存1个字符,MySQL会额外找一个字节存放长度标识

样例

公式应该都理解了:VARCHAR的最大长度=(最大行大小(65535)- NULL标识列占用字节数-长度标识字节数)/字符集单字节最大字节数。有余数向下取整

接下来通过实验来验证,为了便于理解计算,例子做了一些调整:

  • 不设置可控列、这样可以去掉NULL标识列
  • 为了便于体现长度标识位的差距,采用多了列的形式放大其存在
  • 为了体现按可能字节数计算长度,这里采用多字节的字符集GBK

创建一个表,包含2个非空VARCHAR(127),每个列可能的最大字节数为254、长度标识位是1字节。那么还可以添加最大65535-12722(2个列,每个列最大占1272个字节)-12(2个长度标识位)=65023字节非空VARCHAR列,约等于32511个字符:

-- 测试边界值32512,确认仍然过大
CREATE TABLE test_varchar_length4(
    v1 VARCHAR(127) NOT NULL,
    v2 VARCHAR(127) NOT NULL,
    vm VARCHAR(32512) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=GBK

> 1118 - 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字节
CREATE TABLE test_varchar_length4(
    v1 VARCHAR(127) NOT NULL,
    v2 VARCHAR(127) NOT NULL,
    vm VARCHAR(32511) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=GBK
> OK

接下来将两个字段调大到128字符,每个列可能的最大字节数为256、理论上长度标识位是2字节。那么还可以添加最大65535 - 128*2*2(2个列,每个列最大占127*2个字节)- 2*2(2个长度标识位) = 65017字节的非空VARCHAR列,约等于32508个字符:

-- 测试边界值32509,确认仍然过大
CREATE TABLE test_varchar_length5(
    v1 VARCHAR(128) NOT NULL,
    v2 VARCHAR(128) NOT NULL,
    vm VARCHAR(32509)
)ENGINE=INNODB DEFAULT CHARSET=GBK

> 1118 - 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字节
CREATE TABLE test_varchar_length5(
    v1 VARCHAR(128) NOT NULL,
    v2 VARCHAR(128) NOT NULL,
    vm VARCHAR(32508)
)ENGINE=INNODB DEFAULT CHARSET=GBK

> OK

小结

  • 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)

所以MySQL要存储”中国cn”需要的是10.12511字节。

回到开始的问题,i的最大字符数为216-1(varchar的最大字节长度)- 2X9(9列的长度标识符消耗的字节数)-9/8(9列允许空,1列NULL就用 1 bit 标识,那么就是9 bit,8bit=1byte)-8X7279 = 7283,所以i列最大能容纳的字符数在这里为7283

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!
未填写
文章
247
粉丝
17
喜欢
213
收藏
58
排名:732
访问:9674
私信
所有博文
社区赞助商