MySQL MySQL中VARCHAR和TEXT的区别

Difference between VARCHAR and TEXT in MySQL

提问by user3840485

When we create a table in MySQL with a VARCHARcolumn, we have to set the length for it. But for TEXTtype we don't have to provide the length.

当我们在 MySQL 中创建带有VARCHAR列的表时，我们必须为其设置长度。但是对于TEXT类型，我们不必提供长度。

What are the differences between VARCHARand TEXT?

VARCHAR和之间有什么区别TEXT？

回答by AbcAeffchen

TL;DR

TL; 博士

TEXT

TEXT

• fixed max size of 65535 characters (you cannot limit the max size)
• takes 2 + cbytes of disk space, where cis the length of the stored string.
• cannot be (fully) part of an index. One would need to specify a prefix length.

• 固定最大大小为 65535 个字符（您不能限制最大大小）
• 占用 2 +c字节的磁盘空间，其中c是存储字符串的长度。
• 不能（完全）是索引的一部分。需要指定前缀长度。

VARCHAR(M)

VARCHAR(M)

• variable max size of Mcharacters
• Mneeds to be between 1 and 65535
• takes 1 + cbytes (for M≤ 255) or 2 + c(for 256 ≤ M≤ 65535) bytes of disk space where cis the length of the stored string
• can be part of an index

• 可变最大M字符大小
• M需要在 1 到 65535 之间
• 占用 1 +c字节（对于M≤ 255）或 2 + c（对于 256 ≤ M≤ 65535）字节的磁盘空间，其中c是存储字符串的长度
• 可以是索引的一部分

More Details

更多细节

TEXThas a fixedmax size of 21?-1 = 65535characters.
VARCHARhas a variablemax size Mup toM = 21?-1.
So you cannot choose the size of TEXTbut you can for a VARCHAR.

TEXT有固定的最大21?-1 = 65535字符大小。
VARCHAR有一个可变的最大大小M可达M = 21?-1.
因此，您无法选择 的大小，TEXT但可以选择VARCHAR.

The other difference is, that you cannot put an index (except for a fulltext index) on a TEXTcolumn.
So if you want to have an index on the column, you have to use VARCHAR. But notice that the length of an index is also limited, so if your VARCHARcolumn is too long you have to use only the first few characters of the VARCHARcolumn in your index (See the documentation for CREATE INDEX).

另一个区别是，您不能在TEXT列上放置索引（全文索引除外）。
所以如果你想在列上有一个索引，你必须使用VARCHAR. 但请注意，索引的长度也是有限的，因此如果您的VARCHAR列太长，您必须仅使用VARCHAR索引中列的前几个字符（请参阅 文​​档CREATE INDEX）。

But you also want to use VARCHAR, if you know that the maximum length of the possible input string is only M, e.g. a phone number or a name or something like this. Then you can use VARCHAR(30)instead of TINYTEXTor TEXTand if someone tries to save the text of all three "Lord of the Ring" books in your phone number column you only store the first 30 characters :)

但是您也想使用VARCHAR，如果您知道可能的输入字符串的最大长度仅为M，例如电话号码或姓名或类似的东西。然后您可以使用VARCHAR(30)代替TINYTEXT或TEXT，如果有人试图在您的电话号码栏中保存所有三本“指环王”书籍的文本，您只存储前 30 个字符:)

Edit:If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXTor LONGTEXT, but be careful: MEDIUMTEXTstores strings up to 16 MB, LONGTEXTup to 4 GB. If you use LONGTEXTand get the data via PHP (at least if you use mysqliwithout store_result), you maybe get a memory allocation error, because PHP tries to allocate 4 GB of memory to be sure the whole string can be buffered. This maybe also happens in other languages than PHP.

编辑：如果要存储在数据库中的文本长度超过 65535 个字符，则必须选择MEDIUMTEXT或LONGTEXT，但要小心：MEDIUMTEXT存储的字符串最大为 16 MB，LONGTEXT最大为 4 GB。如果您LONGTEXT通过 PHP使用和获取数据（至少如果您mysqli不使用store_result），您可能会遇到内存分配错误，因为 PHP 尝试分配 4 GB 内存以确保可以缓冲整个字符串。这可能也发生在 PHP 之外的其他语言中。

However, you should alwayscheck the input (Is it too long? Does it contain strange code?) beforestoring it in the database.

但是，在将其存储到数据库之前，您应该始终检查输入（是否太长？是否包含奇怪的代码？）。

Notice: For both types, the required disk space depends only on the length of the stored string and not on the maximum length.
E.g.if you use the charset latin1 and store the text "Test" in VARCHAR(30), VARCHAR(100)and TINYTEXT, it always requires 5 bytes (1 byte to store the length of the string and 1 byte for each character). If you store the same text in a VARCHAR(2000)or a TEXTcolumn, it would also require the same space, but, in this case, it would be 6 bytes (2 bytes to store the string length and 1 byte for each character).

注意：对于这两种类型，所需的磁盘空间仅取决于存储字符串的长度，而不取决于最大长度。
例如，如果您使用字符集 latin1 并将文本“Test”存储在VARCHAR(30), VARCHAR(100)and 中TINYTEXT，则它始终需要 5 个字节（1 个字节用于存储字符串的长度，1 个字节用于存储每个字符）。如果您在 aVARCHAR(2000)或TEXT列中存储相同的文本，它也需要相同的空间，但在这种情况下，它将是 6 个字节（2 个字节用于存储字符串长度，1 个字节用于存储每个字符）。

For more information have a look at the documentation.

有关更多信息，请查看文档。

Finally, I want to add a notice, that both, TEXTand VARCHARare variable length data types, and so they most likely minimize the space you need to store the data. But this comes with a trade-off for performance. If you need better performance, you have to use a fixed length type like CHAR. You can read more about this here.

最后，我想补充一点，TEXT和VARCHAR都是可变长度数据类型，因此它们很可能会最小化存储数据所需的空间。但这需要对性能进行权衡。如果您需要更好的性能，则必须使用固定长度类型，如CHAR. 您可以在此处阅读更多相关信息。

回答by Code Commander

There is an important detail that has been omitted in the answer above.

上面的答案中省略了一个重要的细节。

MySQL imposes a limit of 65,535 bytes for the max size of each row. The size of a VARCHARcolumn is counted towards the maximum row size, while TEXTcolumns are assumed to be storing their data by reference so they only need 9-12 bytes. That means even if the "theoretical" max size of your VARCHARfield is 65,535 characters you won't be able to achieve that if you have more than one column in your table.

MySQL 对每行的最大大小施加了 65,535 字节的限制。VARCHAR列的大小计入最大行大小，而TEXT列被假定为通过引用存储其数据，因此它们只需要 9-12 个字节。这意味着即使您的VARCHAR字段的“理论”最大大小是 65,535 个字符，如果您的表中有不止一列，您也无法实现这一点。

Also note that the actual number of bytes required by a VARCHARfield is dependent on the encoding of the column (and the content). MySQL counts the maximum possible bytes used toward the max row size, so if you use a multibyte encoding like utf8mb4(which you almost certainly should) it will use up even more of your maximum row size.

另请注意，VARCHAR字段所需的实际字节数取决于列（和内容）的编码。MySQL 将使用的最大可能字节数计入最大行大小，因此如果您使用多字节编码utf8mb4（您几乎肯定应该这样做），它将使用更多的最大行大小。

Correction: Regardless of how MySQL computes the max row size, whether or not the VARCHAR/TEXTfield data is ACTUALLY stored in the row or stored by reference depends on your underlying storage engine. For InnoDB the row formataffects this behavior. (Thanks Bill-Karwin)

更正：无论MySQL如何计算最大行大小， VARCHAR/TEXT字段数据是实际存储在行中还是通过引用存储取决于您的底层存储引擎。对于 InnoDB，行格式会影响此行为。（感谢比尔-卡文）

Reasons to use TEXT:

使用理由TEXT：

• If you want to store a paragraph or more of text
• If you don't need to index the column
• If you have reached the row size limit for your table

• 如果要存储一段或更多文本
• 如果您不需要索引该列
• 如果您已达到表的行大小限制

Reasons to use VARCHAR:

使用理由VARCHAR：

• If you want to store a few words or a sentence
• If you want to index the (entire) column
• If you want to use the column with foreign-key constraints

• 如果你想存储几个单词或一个句子
• 如果要索引（整个）列
• 如果要使用带有外键约束的列