什么是MySQL VARCHAR最大尺寸?

时间：2022-09-27 08:56:10

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)。我可以设定的最大值是多少?

8 个解决方案

#1

202  

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和文本类型。BLOB和文本列仅向行大小限制贡献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,535 in 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列中的值是可变长度字符串。该长度可以指定为从0到255的值，然后是MySQL 5.0.3，在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 TEXT types inorder to overcome row size limit.

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

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和文本类型的详细信息。

Even more

更

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

检查数据类型存储需求的更多细节，它处理所有数据类型的存储需求。

#2

49  

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 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 rather than "real" UTF-8, which can have up to four bytes).

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

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 BLOB or TEXT types. 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.

如果您想要使用更多，您可以使用BLOB或文本类型。这些不计入64 k限制的行(除了一个小行政足迹)但是你需要注意的其他问题,来自他们的使用,如不能排序使用整个文本块超出一定数量的字符(虽然这向上可以配置),迫使临时表在磁盘上而不是在内存中,或者配置客户端和服务器通讯有效处理大小的缓冲区。

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列可以存储“仅”大约50万个字符，(16M-1)/3 = 5,592,405)，但它仍然大大扩展了您的范围。

#3

27  

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

字符集utf8 VARCHAR(21844)

#4

6  

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可以存储16777,213字节，而LONGBLOB可以存储最多4,294,967,292字节。

#5

6  

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字符可以要求每个字符最多3个字节，因此使用utf8字符集的VARCHAR列可以被声明为最多21844个字符。

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个字节，从而导致字符限制为21844。但是如果您使用的是现代多字节字符集utf8mb4，您应该使用它!它支持表情符号和其他特殊字符。它将使用每个字符4个字节。这将限制每个表的字符数为16,383。注意，其他字段，例如INT也将被计算到这些限制。

Conclusion:

结论:

utf8 maximum of 21,844 characters

utf8最大值为21844个字符。

utf8mb4 maximum of 16,383 characters

utf8mb4最大16383个字符。

#6

1  

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

#7

-1  

MySQL VARCHAR max size is 65,535 bytes. It can be store 65,535 characters.

MySQL VARCHAR最大大小为65,535字节。它可以存储65,535个字符。

#8

-2  

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

您可以使用文本类型，它不限于64KB。

#1

202  

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和文本类型。BLOB和文本列仅向行大小限制贡献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,535 in 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列中的值是可变长度字符串。该长度可以指定为从0到255的值，然后是MySQL 5.0.3，在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 TEXT types inorder to overcome row size limit.

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

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和文本类型的详细信息。

Even more

更

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

检查数据类型存储需求的更多细节，它处理所有数据类型的存储需求。

#2

49  

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 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 rather than "real" UTF-8, which can have up to four bytes).

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

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 BLOB or TEXT types. 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.

如果您想要使用更多，您可以使用BLOB或文本类型。这些不计入64 k限制的行(除了一个小行政足迹)但是你需要注意的其他问题,来自他们的使用,如不能排序使用整个文本块超出一定数量的字符(虽然这向上可以配置),迫使临时表在磁盘上而不是在内存中,或者配置客户端和服务器通讯有效处理大小的缓冲区。

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列可以存储“仅”大约50万个字符，(16M-1)/3 = 5,592,405)，但它仍然大大扩展了您的范围。

#3

27  

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

字符集utf8 VARCHAR(21844)

#4

6  

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可以存储16777,213字节，而LONGBLOB可以存储最多4,294,967,292字节。

#5

6  

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字符可以要求每个字符最多3个字节，因此使用utf8字符集的VARCHAR列可以被声明为最多21844个字符。

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个字节，从而导致字符限制为21844。但是如果您使用的是现代多字节字符集utf8mb4，您应该使用它!它支持表情符号和其他特殊字符。它将使用每个字符4个字节。这将限制每个表的字符数为16,383。注意，其他字段，例如INT也将被计算到这些限制。

Conclusion:

结论:

utf8 maximum of 21,844 characters

utf8最大值为21844个字符。

utf8mb4 maximum of 16,383 characters

utf8mb4最大16383个字符。

#6

1  

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

#7

-1  

MySQL VARCHAR max size is 65,535 bytes. It can be store 65,535 characters.

MySQL VARCHAR最大大小为65,535字节。它可以存储65,535个字符。

#8

-2  

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

您可以使用文本类型，它不限于64KB。

标签：

相关文章