MySQL VARCHAR 最大大小是多少？

What is the MySQL VARCHAR max size?

提问by user1832628

I would like to know what the max size is for a MySQL VARCHAR type.

我想知道 MySQL VARCHAR 类型的最大大小是多少。

I read that the max size is limited by the row size which is about 65k. I tried setting the field to varchar(20000)but it says that that's too large.

我读到最大大小受行大小限制，行大小约为 65k。我尝试将字段设置为，varchar(20000)但它说这太大了。

I could set it to varchar(10000). What is the exact max I can set it to?

我可以将其设置为varchar(10000). 我可以设置的确切最大值是多少？

回答by rajukoyilandy

Keep in mind that MySQL has a maximum row size limit

请记住，MySQL 有最大行大小限制

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, not counting BLOB and TEXT types. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row. Read more about Limits on Table Column Count and Row Size.

MySQL 表的内部表示的最大行大小限制为 65,535 字节，不包括 BLOB 和 TEXT 类型。BLOB 和 TEXT 列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。阅读有关表列数和行大小限制的更多信息。

Maximum size a single column can occupy, is different before and after MySQL 5.0.3

单个列可占用的最大大小，MySQL 5.0.3前后不同

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

VARCHAR 列中的值是可变长度的字符串。在 MySQL 5.0.3 之前，长度可以指定为 0 到 255 之间的值，在 5.0.3 及更高版本中可以指定为 0 到65,535。MySQL 5.0.3 及更高版本中 VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和使用的字符集的约束。

However, note that the limit is lower if you use a multi-byte character set like utf8 or utf8mb4.

但是，请注意，如果您使用像 utf8 或 utf8mb4 这样的多字节字符集，则限制会更低。

Use TEXTtypes inorder to overcome row size limit.

使用TEXT类型来克服行大小限制。

The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

四种文本类型是 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们对应于四种 BLOB 类型，并且具有相同的最大长度和存储要求。

More details on BLOB and TEXT Types

有关 BLOB 和 TEXT 类型的更多详细信息

• Ref for MySQLv8.0 https://dev.mysql.com/doc/refman/8.0/en/blob.html
• Ref for MySQLv5.7 http://dev.mysql.com/doc/refman/5.7/en/blob.html
• Ref for MySQLv5.6 http://dev.mysql.com/doc/refman/5.6/en/blob.html
• Ref for MySQLv5.5 http://dev.mysql.com/doc/refman/5.5/en/blob.html
• Ref for MySQLv5.1 http://dev.mysql.com/doc/refman/5.1/en/blob.html
• Ref for MySQLv5.0 http://dev.mysql.com/doc/refman/5.0/en/blob.html

• MySQLv8.0 参考https://dev.mysql.com/doc/refman/8.0/en/blob.html
• MySQLv5.7 的参考http://dev.mysql.com/doc/refman/5.7/en/blob.html
• MySQLv5.6 的参考http://dev.mysql.com/doc/refman/5.6/en/blob.html
• MySQLv5.5 的参考http://dev.mysql.com/doc/refman/5.5/en/blob.html
• MySQLv5.1 的参考http://dev.mysql.com/doc/refman/5.1/en/blob.html
• MySQLv5.0 参考http://dev.mysql.com/doc/refman/5.0/en/blob.html

Even more

更

Checkout more details on Data Type Storage Requirementswhich deals with storage requirements for all data types.

查看有关处理所有数据类型的存储要求的数据类型存储要求的更多详细信息。

回答by paxdiablo

As per the online docs, there is a 64K row limit and you can work out the row size by using:

根据在线文档，有 64K 行限制，您可以使用以下方法计算行大小：

row length = 1
             + (sum of column lengths)
             + (number of NULL columns + delete_flag + 7)/8
             + (number of variable-length columns)

You need to keep in mind that the column lengths aren't a one-to-one mapping of their size. For example, CHAR(10) CHARACTER SET utf8requires three bytes for each of the ten characters since that particular encoding has to account for the three-bytes-per-character property of utf8(that's MySQL's utf8encodingrather than "real" UTF-8, which can have up to four bytes).

您需要记住，列长度不是它们大小的一对一映射。例如，CHAR(10) CHARACTER SET utf8十个字符中的每一个都需要三个字节，因为该特定编码必须考虑utf8（这是MySQL 的utf8编码，而不是“真正的”UTF-8，最多可以有四个字节）的每字符三个字节的属性）。

But, if your row size is approaching 64K, you may want to examine the schema of your database. It's a rare table that needs to be that wide in a properly set up (3NF) database - it's possible,just not very common.

但是，如果您的行大小接近 64K，您可能需要检查数据库的架构。在正确设置的 (3NF) 数据库中需要那么宽的表是罕见的 - 这是可能的，只是不是很常见。

If you want to use more than that, you can use the BLOBor TEXTtypes. These do not count against the 64K limit of the row (other than a small administrative footprint) but you need to be aware of other problems that come from their use, such as not being able to sort using the entire text block beyond a certain number of characters (though this can be configured upwards), forcing temporary tables to be on disk rather than in memory, or having to configure client and server comms buffers to handle the sizes efficiently.

如果你想使用更多，你可以使用BLOBorTEXT类型。这些不计入行的 64K 限制（除了小的管理占用空间），但您需要注意使用它们带来的其他问题，例如无法使用超过特定数字的整个文本块进行排序字符数（尽管这可以向上配置），强制临时表位于磁盘而不是内存中，或者必须配置客户端和服务器通信缓冲区以有效处理大小。

The sizes allowed are:

允许的尺寸是：

TINYTEXT          255 (+1 byte  overhead)
TEXT          64K - 1 (+2 bytes overhead)
MEDIUMTEXT    16M - 1 (+3 bytes overhead)
LONGTEXT      4G  - 1 (+4 bytes overhead)

You still have the byte/character mismatch (so that a MEDIUMTEXT utf8column can store "only" about half a million characters, (16M-1)/3 = 5,592,405) but it still greatly expands your range.

您仍然存在字节/字符不匹配（因此一MEDIUMTEXT utf8列可以“仅”存储大约 50 万个字符(16M-1)/3 = 5,592,405），但它仍然大大扩展了您的范围。

回答by Attila

Source

来源

The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):

VARCHAR(65535) However, note that the limit is lower if you use a multi-byte character set:

VARCHAR(21844) CHARACTER SET utf8

varchar 的最大长度取决于 MySQL 中的最大行大小，即 64KB（不包括 BLOB）：

VARCHAR(65535) 但是，请注意，如果使用多字节字符集，限制会更低：

VARCHAR(21844) 字符集 utf8

回答by Firze

From MySQL documentation:

来自 MySQL 文档：

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

MySQL 5.0.3 及更高版本中 VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和使用的字符集的约束。例如，utf8 字符可能需要每个字符最多三个字节，因此可以将使用 utf8 字符集的 VARCHAR 列声明为最多 21,844 个字符。

Limits for the VARCHAR varies depending on charset used. Using ASCII would use 1 byte per character. Meaning you could store 65,535 characters. Using utf8 will use 3 bytes per character resulting in character limit of 21,844. BUT if you are using the modern multibyte charset utf8mb4 which you should use! It supports emojis and other special characters. It will be using 4 bytes per character. This will limit the number of characters per table to 16,383. Note that other fields such as INT will also be counted to these limits.

VARCHAR 的限制因使用的字符集而异。使用 ASCII 将使用每个字符 1 个字节。这意味着您可以存储 65,535 个字符。使用 utf8 将使用每个字符 3 个字节，导致字符限制为 21,844。但是，如果您使用的是现代多字节字符集 utf8mb4，您应该使用它！它支持表情符号和其他特殊字符。每个字符将使用 4 个字节。这会将每个表的字符数限制为 16,383。请注意，其他字段（例如 INT）也将计入这些限制。

Conclusion:

结论：

utf8maximum of 21,844characters

utf8最多21,844 个字符

utf8mb4maximum of 16,383characters

utf8mb4最多16,383 个字符

回答by SRIRAM

you can also use MEDIUMBLOB/LONGBLOB or MEDIUMTEXT/LONGTEXT

您还可以使用 MEDIUMBLOB/LONGBLOB 或 MEDIUMTEXT/LONGTEXT

A BLOB type in MySQL can store up to 65,534 bytes, if you try to store more than this much data MySQL will truncate the data. MEDIUMBLOB can store up to 16,777,213 bytes, and LONGBLOB can store up to 4,294,967,292 bytes.

MySQL 中的 BLOB 类型最多可以存储 65,534 个字节，如果您尝试存储超过这个数量的数据，MySQL 将截断数据。MEDIUMBLOB 最多可存储 16,777,213 个字节，LONGBLOB 最多可存储 4,294,967,292 个字节。

回答by Suresh Kamrushi

Before Mysql version 5.0.3 Varchar datatype can store 255 character, but from 5.0.3 it can be store 65,535 characters.

Mysql 5.0.3 之前的 Varchar 数据类型可以存储 255 个字符，但从 5.0.3 开始可以存储 65,535 个字符。

BUT it has a limitation of maximum row size of 65,535 bytes. It means including all columns it must not be more than 65,535 bytes.

但它的最大行大小限制为 65,535 字节。这意味着包括所有列，它不得超过 65,535 个字节。

In your case it may possible that when you are trying to set more than 10000 it is exceeding more than 65,535 and mysql will gives the error.

在您的情况下，当您尝试设置超过 10000 时，它可能会超过 65,535，并且 mysql 会给出错误。

For more information: https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

更多信息：https: //dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

blog with example: http://goo.gl/Hli6G3

博客示例：http: //goo.gl/Hli6G3

回答by mvp

You can use TEXTtype, which is not limited to 64KB.

您可以使用TEXTtype，它不限于 64KB。