MySQL 的 varchar 字段最大长度真的是 65535 吗？

数据与人 2024-03-26

1088

将 "数据与人" 设为 "星标⭐"

第一时间收到文章更新

在 MySQL 数据库的设计过程中，我们经常需要为表中的某些字段定义字符串类型。

CREATE TABLE `user` (
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

以用户表为例，用户名通常就是一个字符串。在 MySQL 中，处理字符串数据的常见数据类型是 `CHAR` 和 `VARCHAR` 。

使用这两种数据类型时，都需要在字段定义后指定一个数字，如 `CHAR(100)` 或 `VARCHAR(100)` ，这里的数字100代表该字段能够存储的最大字符数。

不过，`CHAR` 和 `VARCHAR` 在存储机制上有所不同：

`VARCHAR` 字段会根据实际存储的字符串长度动态调整存储空间；
`CHAR` 字段则会预先分配指定长度的空间，无论实际存储的字符串有多长。

因此，`VARCHAR` 通常更节省存储空间，也是在设计数据库时更倾向选择的类型。

那么，`VARCHAR` 字段的最大长度是多少呢？

你可能听说过是 65535 个字符。

但实际情况是否如此呢？接下来，我们将通过实验来验证这一点。

varchar 最大值是多少？

我们直接拿 65535 来试一下。

当我们尝试使用长度为 65535 的 VARCHAR 字段时，报错了：提示列长度过大。

实际的最大长度限制是 16383。将长度设置为 16383 时，操作能够成功执行。

这是不是意味着 VARCHAR 的最大长度就是 16383。带着疑问继续往下看。

实际上，这个最大值受到多个因素的影响。

不同字符集的影响

VARCHAR 字段用于存储字符串，这些字符串可以是英文字母、数字或中文。不论存放什么，这些字符串最终都会被转换为二进制的 01 串。

这种将符号与二进制代码关联起来的过程被称为编码。当多个已编码的字符聚集在一起时，它们就构成了我们常说的字符集。

在建表语句中，有一个名为 CHARSET 的部分，用于指定字符集。

不同的字符集对字节的使用要求不同。通过执行 `show charset;` 命令，我们可以查看MySQL支持的字符集及其存储一个字符所需的最大字节数（Maxlen）。

如果我们尝试更改建表 SQL 语句中的 CHARSET，例如更改为 utf8mb3，并再次执行，我们会发现最大值的限制会有所变化。

尽管 utf8mb3 字符集下的报错提示最大值为 21845，但实际操作中，只有当该值设为 21844 时才会成功。

当我们将字符集更改为latin1时，会发现最大值变成了 65533。

varchar为65533时创建成功

从这一系列的操作中，我们可以观察到一些规律：

utf8mb4 的 maxlen 为 4，对应的 VARCHAR 最大长度为 16383。两者相乘的结果是 65532。
utf8mb3 的 maxlen 为3，对应的 VARCHAR 最大长度为 21844。两者相乘的结果同样是 65532。
latin1 的 maxlen 为 1，对应的 VARCHAR 最大长度为 65533。两者相乘的结果是 65533。

由此可以推断，VARCHAR 旁边的长度代表这一列能存放的最大字符数，而maxlen 则代表单个字符占用的最大字节数。

这两者的乘积非常接近 65535，说明 65535 实际上是指字节数，而不是字符数。

因此，VARCHAR 的最大长度会根据所选择的字符集而有所不同，通常接近于65535 除以字符集的 maxlen。

但请注意，这还不是完全严谨的结论，因为还有其他因素也会影响 VARCHAR 的最大长度。

NULL是否会影响？

在前面的建表语句中，当我们将 `test` 字段从 `NOT NULL` 修改为可以为`NULL` 时，并使用 `CHARSET=latin1`，我们发现之前允许的最大长度 65533 现在会导致建表失败，而改为 65532 则能成功。

这是因为一个字段是否为 `NULL` 需要占用一个字节来记录。当字段设置为`NOT NULL` 时，这个字节可以节省下来。

列数是否会影响？

此外，我们还需要考虑表中列数的影响。当我们在表中增加更多的列时，`VARCHAR` 字段的最大长度还会发生变化。例如，当使用 `latin1` 字符集并尝试在表中添加第二个 `VARCHAR` 列，且都使用前面允许的最大值 65533 时，我们会发现建表会失败。

我们得知 MySQL 单行的最大长度（不包括 `BLOB` 和 `TEXT` 等类型）是65535 个字节。

这意味着表中所有列加起来（不考虑其他隐藏列和记录头信息）占用的最大长度不能超过 65535 个字节。

因此，前面提到的 `VARCHAR` 字段的最大长度，实际上是接近于 65535 除以字符集的 `maxlen`。但这个前提是表中只有一列 `NOT NULL` 的 `VARCHAR` 类型字段。如果有其他类型的列（如 `INT` 或 `BIGINT` ），它们会占用额外的字节，从而减少单个 `VARCHAR` 列可用的空间。

综上所述，`VARCHAR` 字段的最大长度受到多个因素的影响，包括是否允许为`NULL`、表中其他列的存在以及字符集的选择。因此，在实际应用中，我们需要根据具体的需求和约束来确定 `VARCHAR` 字段的最大长度。

列为什么不是 65535 而是 65533 ？

我们再次观察之前的建表 SQL，无论使用哪种字符集，得到的 VARCHAR 字段的字符数总是接近 65533 。然而，一个 MySQL 数据行的最大字节长度是65535 。那么，65535 减去65533 等于 2，这缺失的 2 个字节是怎么回事呢？

要解答这个问题，我们需要深入了解 MySQL 数据行的存储格式。通过 `SHOW TABLE STATUS` 命令，我们可以查看当前数据表使用的行格式。

通常情况下，MySQL数据表使用 Dynamic 行格式。

Dynamic行格式将行记录分为两部分：行记录的额外信息和行记录的真实数据。

额外信息包括变长字段的长度列表（如 VARCHAR、TEXT、BLOB 字段的长度）和 NULL 值列表（记录哪些列的值是NULL）。如果所有列都是NOT NULL，那么这个列表就不需要。此外，还有5个字节的记录头信息，用于存储一些特殊信息，如行的删除状态、在数据页中的位置等。
真实数据部分则包含行中每一列的实际内容。除了我们明确创建的列外，还有一些隐藏列，如Row_ID（当表没有声明主键时自动生成）和事务相关的字段，如trx_id（记录修改该行的事务ID）和roll_pointer（指向该行上一个版本的指针，用于实现多版本并发控制）。

回到我们的问题，为什么 65535 字节的行最大值减去 VARCHAR 字段的字符数（约 65533）会剩下 2 个字节呢？

这是因为在 Dynamic 行格式中，变长字段的长度列表需要占用一定的空间。对于 VARCHAR 字段，它使用两个字节来存储长度信息（16位，最大可表示65535）。因此，当我们为VARCHAR字段分配65533个字符的空间时，实际上还需要额外的2个字节来存储这个长度信息。所以，65535字节的行最大值减去VARCHAR字段的字符数（约65533）和这2个字节的长度信息，正好是0，这解释了为什么我们看不到任何额外的空间消耗。

页大小 16k，怎么保存 65533（64k）的数据？

在MySQL中，数据通常是以页为单位进行存储的，每页大小为16KB。然而，某些字段，如 VARCHAR，可能包含超过16KB的数据。

当VARCHAR字段的数据长度超过页的大小时，MySQL如何处理呢？

为了解决这个问题，MySQL 使用了一种称为“行溢出”的技术。当 VARCHAR 字段的大小超过一页时，行记录中只存储一个20字节的指针（实际上是一个偏移量），该指针指向存储实际数据的“溢出页”。这样，即使 VARCHAR 字段的数据很大，也可以将其存储在多个页中，而不仅仅是一个数据页中。

大于 64k 的字符串怎么处理？

那么，如果我们需要存储的数据量超过 64KB，即超过 VARCHAR 的最大长度，该怎么办呢？

在这种情况下，我们可以使用TEXT或BLOB类型的字段。这些字段类型也分为TINY、MEDIUM和LONG三个级别，以适应不同大小的数据，最大可以达到4GB左右。

与 VARCHAR 类似，当 TEXT 或 BLOB 字段的数据超过一页大小时，MySQL也会使用行溢出技术，只在行记录中存储一个指向实际数据的指针。

这意味着，当我们需要读取包含大型TEXT或BLOB字段的行时，可能需要从多个页中读取数据，从而导致更多的磁盘I/O操作，进而可能影响性能。

因此，为了优化性能，当编写 SELECT 语句时，我们应该尽量避免选择包含大型 TEXT 或 BLOB 字段的列，除非确实需要它们。这也是为什么通常不建议使用 “SELECT * FROM table” 语句的原因，因为它可能会选择包含大型字段的所有列。

此外，BLOB 和 TEXT 字段类型在处理数据时有一些区别。BLOB 字段没有字符集的概念，而TEXT字段有。

这意味着，如果我们使用BLOB字段来存储文本数据，我们将无法使用字符集的校对规则来对其进行排序或比较。

BLOB字段还可以用于存储二进制数据，如压缩的文本、图片或视频等。然而，尽管技术上可行，但通常不建议使用BLOB字段来存储大型图片或视频文件，因为它们更适合存储在对象存储服务（Object Storage Service，简称OSS）中。



更多精彩内容，关注我们▼▼

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