MySQL中VARCHAR和TEXT的区别 [英] Difference between VARCHAR and TEXT in MySQL

问题描述

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

When we create a table in MySQL with a VARCHAR column, we have to set the length for it. But for TEXT type we don't have to provide the length.

VARCHAR 和 TEXT 有什么区别?

推荐答案

TL;DR

TEXT

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

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

VARCHAR(M)

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

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

TEXT 的固定最大大小为 2¹⁶-1 = 65535 个字符.
VARCHAR 有一个 变量 最大大小 M 高达 M = 2¹⁶-1.
所以你不能选择TEXT的大小，但你可以选择VARCHAR.

TEXT has a fixed max size of 2¹⁶-1 = 65535 characters.
VARCHAR has a variable max size M up to M = 2¹⁶-1.
So you cannot choose the size of TEXT but you can for a VARCHAR.

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

The other difference is, that you cannot put an index (except for a fulltext index) on a TEXT column.
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 VARCHAR column is too long you have to use only the first few characters of the VARCHAR column in your index (See the documentation for CREATE INDEX).

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

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 TINYTEXT or TEXT and 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 :)

如果要存储在数据库中的文本长度超过65535个字符，则必须选择MEDIUMTEXT或LONGTEXT，但要小心:MEDIUMTEXT 最多可存储 16 MB 的字符串，LONGTEXT 最多可存储 4 GB.如果您使用 LONGTEXT 并通过 PHP 获取数据(至少如果您使用 mysqli 而没有 store_result)，您可能会收到内存分配错误，因为 PHP 尝试分配 4 GB 的内存以确保可以缓冲整个字符串.这可能也发生在 PHP 之外的其他语言中.

If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXT or LONGTEXT, but be careful: MEDIUMTEXT stores strings up to 16 MB, LONGTEXT up to 4 GB. If you use LONGTEXT and get the data via PHP (at least if you use mysqli without 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.

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

However, you should always check the input (Is it too long? Does it contain strange code?) before storing it in the database.

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

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 TEXT column, 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).

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

For more information have a look at the documentation.

最后，我想补充一点，TEXT 和 VARCHAR 都是可变长度数据类型，因此它们很可能最大限度地减少了您需要存储的空间数据.但这需要对性能进行权衡.如果您需要更好的性能，则必须使用像 CHAR 这样的固定长度类型.您可以在此处阅读有关此内容的更多信息.

Finally, I want to add a notice, that both, TEXT and VARCHAR are 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.

这篇关于MySQL中VARCHAR和TEXT的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！