MySQL VARCHAR的最大大小是多少？

港控/mmm° 2022-11-27 15:42 208阅读 0赞

本文翻译自：[What is the MySQL VARCHAR max size?][What is the MySQL VARCHAR max size]

*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.* **我读到最大大小受行大小限制，大约65k。** *I tried setting the field to `varchar(20000)` but it says that that's too large.* **我尝试将字段设置为`varchar(20000)`但它表示太大。**

*I could set it to `varchar(10000)` .* **我可以将其设置为`varchar(10000)` 。** *What is the exact max I can set it to?* **我可以将其设置为多少？**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/UFK8/MySQL-VARCHAR的最大大小是多少][https_stackoom.com_question_UFK8_MySQL-VARCHAR]

--------------------

### \#2楼 ###

您可以使用[`TEXT`类型][TEXT] ，但不限于64KB。

--------------------

### \#3楼 ###

*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.* **MySQL中的BLOB类型最多可存储65,534个字节，如果您尝试存储的数据量过多，则MySQL将截断该数据。** *MEDIUMBLOB can store up to 16,777,213 bytes, and LONGBLOB can store up to 4,294,967,292 bytes.* **MEDIUMBLOB最多可以存储16,777,213字节，而LONGBLOB最多可以存储4,294,967,292字节。**

--------------------

### \#4楼 ###

***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.* **MySQL表的内部表示形式的最大行大小限制为65,535字节，不包括BLOB和TEXT类型。** *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.* **BLOB和TEXT列仅对行大小限制贡献9到12个字节，因为它们的内容与行的其余部分分开存储。** *Read more about [Limits on Table Column Count and Row Size.][]* **阅读有关[表列数和行大小限制的][Limits on Table Column Count and Row Size.]更多信息[。][Limits on Table Column Count and Row Size.]**

***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.* **VARCHAR列中的值是可变长度的字符串。** *The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to **65,535** in 5.0.3 and later versions.* **在MySQL 5.0.3之前，长度可以指定为0到255之间的值，而在5.0.3和更高版本中，长度可以指定为0到65535之间的值 。** *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.* **在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 `TEXT` types inorder to overcome row size limit.*** **使用`TEXT`类型以克服行大小限制。**

> *The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.* **四种TEXT类型是TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT。** *These correspond to the four BLOB types and have the same maximum lengths and storage requirements.* **这些对应于四种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][https_dev.mysql.com_doc_refman_8.0_en_blob.html]* **MySQLv8.0的参考[https://dev.mysql.com/doc/refman/8.0/en/blob.html][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][http_dev.mysql.com_doc_refman_5.7_en_blob.html]* **参考MySQLv5.7 [http://dev.mysql.com/doc/refman/5.7/en/blob.html][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][http_dev.mysql.com_doc_refman_5.6_en_blob.html]* **参考MySQLv5.6 [http://dev.mysql.com/doc/refman/5.6/en/blob.html][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][http_dev.mysql.com_doc_refman_5.5_en_blob.html]* **参考MySQLv5.5 [http://dev.mysql.com/doc/refman/5.5/en/blob.html][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][TEXT]* **MySQLv5.1的参考[http://dev.mysql.com/doc/refman/5.1/en/blob.html][TEXT]**
 *  *Ref for MySQLv5.0 [http://dev.mysql.com/doc/refman/5.0/en/blob.html][http_dev.mysql.com_doc_refman_5.0_en_blob.html]* **MySQLv5.0的参考[http://dev.mysql.com/doc/refman/5.0/en/blob.html][http_dev.mysql.com_doc_refman_5.0_en_blob.html]**

***Even more*** **更**

*Checkout more details on [Data Type Storage Requirements][] which deals with storage requirements for all data types.* **查看有关“ [数据类型存储要求”的][Data Type Storage Requirements]更多详细信息，该信息处理所有数据类型的存储要求。**

--------------------

### \#5楼 ###

*As per [the online docs][] , there is a 64K row limit and you can work out the row size by using:* **根据[在线文档][the online docs] ，行限制为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 utf8` requires 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 `utf8` encoding][MySQL_s _utf8_ encoding] rather than "real" UTF-8, which can have up to four bytes).* **例如， `CHAR(10) CHARACTER SET utf8`的十个字符中的每个字符都需要三个字节，因为该特定编码必须考虑`utf8`的每个字符三字节的属性（这是[MySQL的`utf8`编码，][MySQL_s _utf8_ encoding]而不是“真实的” UTF- 8，最多可以包含四个字节）。**

*But, if your row size is approaching 64K, you may want to examine the schema of your database.* **但是，如果行大小接近64K，则可能需要检查数据库的架构。** *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.* **在一个适当设置的（3NF）数据库中，这是一个罕见的表，它需要那么宽-有*可能，*只是不是很常见。**

*If you want to use more than that, you can use the `BLOB` or `TEXT` types.* **如果要使用更多类型，可以使用`BLOB`或`TEXT`类型。** *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.* **这些不计入该行的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 utf8` column can store "only" about half a million characters, `(16M-1)/3 = 5,592,405` ) but it still greatly expands your range.* **您仍然存在字节/字符不匹配的情况（因此`MEDIUMTEXT utf8`列可以“仅”存储大约一百万个字符， `(16M-1)/3 = 5,592,405` ），但是它仍然大大扩展了您的范围。**

--------------------

### \#6楼 ###

*[Source][]* **[资源][Source]**

> *The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):* **varchar的最大长度取决于MySQL中的最大行大小，即64KB（不包括BLOB）：**
> 
> *VARCHAR(65535) However, note that the limit is lower if you use a multi-byte character set:* **VARCHAR（65535）但是，请注意，如果使用多字节字符集，则该限制会降低：**
> 
> *VARCHAR(21844) CHARACTER SET utf8* **VARCHAR（21844）字符集utf8**

[What is the MySQL VARCHAR max size]: https://oldbug.net/q/UFK8/What-is-the-MySQL-VARCHAR-max-size
[https_stackoom.com_question_UFK8_MySQL-VARCHAR]: https://stackoom.com/question/UFK8/MySQL-VARCHAR%E7%9A%84%E6%9C%80%E5%A4%A7%E5%A4%A7%E5%B0%8F%E6%98%AF%E5%A4%9A%E5%B0%91
[TEXT]: http://dev.mysql.com/doc/refman/5.1/en/blob.html
[Limits on Table Column Count and Row Size.]: http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
[https_dev.mysql.com_doc_refman_8.0_en_blob.html]: https://dev.mysql.com/doc/refman/8.0/en/blob.html
[http_dev.mysql.com_doc_refman_5.7_en_blob.html]: http://dev.mysql.com/doc/refman/5.7/en/blob.html
[http_dev.mysql.com_doc_refman_5.6_en_blob.html]: http://dev.mysql.com/doc/refman/5.6/en/blob.html
[http_dev.mysql.com_doc_refman_5.5_en_blob.html]: http://dev.mysql.com/doc/refman/5.5/en/blob.html
[http_dev.mysql.com_doc_refman_5.0_en_blob.html]: http://dev.mysql.com/doc/refman/5.0/en/blob.html
[Data Type Storage Requirements]: http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
[the online docs]: http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html
[MySQL_s _utf8_ encoding]: https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html
[Source]: https://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql