MySQL中VARCHAR和TEXT之间的区别 [英] Difference between VARCHAR and TEXT in MySQL
问题描述
当我们在MySQL中创建带有VARCHAR
列的表时,我们必须为其设置长度.但是对于TEXT
类型,我们不必提供长度.
VARCHAR
和TEXT
有什么区别?
TL; DR
TEXT
- 固定的最大大小为65535个字符(您不能限制最大大小)
- 占用2 +
c
个字节的磁盘空间,其中<c5>是存储的字符串的长度. - 不能(完全)成为索引的一部分.需要指定一个前缀长度.
VARCHAR(M)
- 可变的最大大小为
M
个字符 -
M
必须介于1到65535之间 - 占用1 +
c
字节(对于M
≤ 255)或2 +c
(对于256≤M
≤ 65535)磁盘空间字节,其中c
是磁盘空间的长度存储的字符串 - 可以是索引的一部分
更多详细信息
TEXT
的固定最大大小为2¹⁶-1 = 65535
个字符.
VARCHAR
的最大大小为变量,M
M = 2¹⁶-1
.
因此,您不能选择TEXT
的大小,但可以选择VARCHAR
的大小.
另一个区别是,不能在TEXT
列上放置索引(全文索引除外).
因此,如果要在该列上创建索引,则必须使用VARCHAR
.但是请注意,索引的长度也受到限制,因此,如果您的VARCHAR
列太长,则必须仅使用索引中VARCHAR
列的前几个字符(请参见VARCHAR(30),VARCHAR(100)
和TINYTEXT
中,则它总是需要5个字节(1个字节用于存储字符串的长度,每个字符1个字节).如果将相同的文本存储在VARCHAR(2000)
或TEXT
列中,则也将需要相同的空间,但是,在这种情况下,它将是6个字节(2个字节用于存储字符串长度,每个存储1个字节)字符).
有关更多信息,请参见文档.
最后,我想说明一下,TEXT
和VARCHAR
都是可变长度的数据类型,因此它们很可能会最小化存储数据所需的空间.但这需要权衡性能.如果需要更好的性能,则必须使用固定长度类型,例如CHAR
.您可以在此处了解更多信息.
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.
What are the differences between VARCHAR
and TEXT
?
TL;DR
TEXT
- fixed max size of 65535 characters (you cannot limit the max size)
- takes 2 +
c
bytes of disk space, wherec
is the length of the stored string. - cannot be (fully) part of an index. One would need to specify a prefix length.
VARCHAR(M)
- variable max size of
M
characters M
needs to be between 1 and 65535- takes 1 +
c
bytes (forM
≤ 255) or 2 +c
(for 256 ≤M
≤ 65535) bytes of disk space wherec
is the length of the stored string - can be part of an index
More Details
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
.
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
).
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 :)
Edit: 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.
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.
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屋!