彻底把MySQL的varchar数据类型讲清楚

运维路书 2022-04-15

494

MySQL中varchar数据类型是数据库中使用非常频繁的数据类型了，但是对于MySQL新手或者从其他数据库转过来的研发小伙儿来说一直有很多疑问，到底varchar后面括号中的数字代表的是字符数还是字节数？varchar到底占多少空间？应该设计多大的长度？为什么扩展varchar长度有时快有时慢？本文将彻底讲解一下varchar，一次性搞清楚它。

本文主要将 varchar占用字节数和扩展varchar字段长度两部分

第一部分：varchar占用字节数

首先对于从其他数据库转移到MySQL的用户要强调的一点：

varchar(10) 括号中的数字代表存储的字符数而不是字节数

varchar每个字符占用的字节数根据不同的编码有所不同：

GBK: 英文、数字, 1个字节；汉字 2个字节
UTF8: 英文、数字, 1个字节；汉字 3个字节
utf8mb4: 英文、数字, 1个字节；汉字 3个字节; emjoy 4个字节

varchar 最大占用字节数计算公式：

以常用编码utf8为例：

varchar最大字节数 = 定义字符数*3 + 长度标识符（1或2个字节）+ NULL标

识列占用字节数

定义字符数：括号中的数字
长度标识符：varchar定义字符数小于等于255时占用1个字节，大于255占用2字节
NULL标识列：可空标识列

在COMPACT、DYNAMIC行格式下，行大小除了数据列长度，还包括可空列标识，即NULL标识位。

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

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

假设一张表中存在N个可空字段，NULL标识位需要⌈N 8 ⌉ （向上取整）个字节。此时整行可用于数据存储的空间只有65535 − ⌈ N 8 ⌉个字节。

实验验证：

验证字符数小于等于255 长度标识符占用1个字节；字符数大于255 长度标识符占用2个字节

MySQL 每一行的最大存储字节数为 65535

实验一：定义两个字段分别为255字节

1. 定义a varchar(85) not null, b varchar(85) not null两个字段两个字段占用字节数为（85*3+1) + (85*3+1) =512

2. 理论上剩余字节数 65535-512= 65023

3. c 字段的理论最大为字符数= (65023-2)/3 = 21673

首先测试c字段的边界值21674

mysql> create table t (a varchar(85) not null,b varchar(85) not null,c varchar(21674) 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 BLOB

果然，在c设置为21674时报错！

mysql> create table t (a varchar(85) not null,b varchar(85) not null,c varchar(21673) not null); 
Query OK, 0 rows affected (0.02 sec)

实验证明：字符数小于等于255 长度标识符占用1个字节

实验二：定义两个字段分别大于255字节

1. 定义a varchar(86) not null, b varchar(86) not null两个字段两个字段占用字节数为（86*3+2) + (86*3+2) =520

2. 剩余字节数 65535-520= 65015

3. c 字段的理论字符数= (65015-2)/3 = 21671

首先测试c字段边界值，

mysql> create table t (a varchar(86) not null,b varchar(86) not null,c varchar(21672) 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

c定义21672报错！

mysql> create table t (a varchar(86) not null,b varchar(86) not null,c varchar(21671) not null); 
Query OK, 0 rows affected (0.03 sec)

21671成功。证明字段长度大于255长度标识符占用2个字节

第二部分：扩展varchar字段长度

MySQL官方手册中对于varchar字段扩展有这样一段描述：

The number of length bytes required by a VARCHAR column must remain the same. 
For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value.
For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, 
in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, 
or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing 
the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. 
In this case, the number of required length bytes changes from 1 to 2, 
which is only supported by a table copy (ALGORITHM=COPY).

总结如下：

varchar字段大小在0-255 bytes 时需要 1 byte 标识符（第一部分已证实），在这个范围内修改字段长度会使用 IN PLACE算法
varchar字段大小在256 bytes以上时需要 2 byte 标识符（第一部分已证实），在这个范围内修改字段长度会使用 COPY算法

in place：此变更由InnoDB引擎独立完成，不需要使用Redo log等，不需要重建表，可以节省开销

COPY算法：需要重建表，同时会导致锁表，影响在线业务

varchar类型字段扩展结论：

表的编码为UTF8的情况下，每个中文字符占3个字节，因为在修改varchar字段长度时varchar(0-85) 可使用 In place算法，不影响在线业务，速度快；varchar(86)以上将采取copy算法需要重建表，开销很大，会锁表，影响在线业务。
表的编码为GBK的情况下，每个中文字符占2个字节，因为在修改varchar字段长度时varchar(0-127) 可使用 In place算法，varchar(128)以上将采取copy算法。

文章转载自运维路书，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

彻底把MySQL的varchar数据类型讲清楚

评论