MySQL 哪种 DATATYPE 更适合使用 TEXT 或 VARCHAR？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1203710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which DATATYPE is better to use TEXT or VARCHAR?
提问by Sasha Chedygov
This question is based on two things performanceand size
这个问题基于性能和大小两件事
Which DATATYPE is better to use TEXT or VARCHAR? Based on performance which will affect and which will impove?
哪种 DATATYPE 更适合使用 TEXT 或 VARCHAR？基于哪些会影响哪些会提升的性能？
回答by Sasha Chedygov
It depends on what you're using it for.I hate to give such a generic answer, but it's true. Generally, try to get the data type as specific as you can. If your strings will never exceed some upper limit of characters, then go with VARCHARbecause it will be a little more efficient. If you need more space, go with TEXT. If you aren't sure how much space your text will take up, you should probably go with TEXT; the performance difference isn't very large, and it's better to be future-proof than risk having to change it later when your requirements change. Just my two cents.
这取决于您使用它的目的。我讨厌给出这样一个笼统的答案，但这是真的。通常，尝试尽可能具体地获取数据类型。如果您的字符串永远不会超过某个字符的上限，那么请使用VARCHAR它，因为它会更有效一些。如果您需要更多空间，请使用TEXT。如果您不确定您的文本将占用多少空间，您可能应该使用TEXT; 性能差异不是很大，并且最好是面向未来而不是在您的需求发生变化时不得不更改它的风险。只有我的两分钱。
In the comments, Pitarou points out that, if MySQL creates a temporary table for your query (see this), TEXTcolumns will not be stored in memory and will have to be read from the disk, which is much slower. (Source, bottom of the page.) This shouldn't matter for most queries, though.
在评论中，Pitarou 指出，如果 MySQL 为您的查询创建一个临时表（请参阅此），TEXT列将不会存储在内存中，而必须从磁盘中读取，这会慢得多。（Source，页面底部。）不过，这对于大多数查询来说并不重要。
In case anyone was wondering how PostgreSQL compares, I found this benchmarkthat shows that CHAR, VARCHAR, and TEXT all perform equally well. So if you're using Postgres, it doesn't matter what type you use.
如果有人想知道 PostgreSQL 的比较情况，我发现这个基准测试表明 CHAR、VARCHAR 和 TEXT 的性能都一样好。因此，如果您使用的是 Postgres，那么您使用的类型并不重要。
回答by Noushad
From V 5.0.3 onwards, Limit of VARCHAR is increased from 0-256 to 0-65,535 (subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.)
从 V 5.0.3 开始，VARCHAR 的限制从 0-256 增加到 0-65,535（取决于最大行大小（65,535 字节，在所有列之间共享）和使用的字符集。）
If you are using TEXT that is fixed 64k length, even if you required lesser limit
如果您使用的是固定 64k 长度的 TEXT，即使您需要较小的限制
So Better to go with VARCHAR with higher limitthan TEXT. If requirement is more than 64K go with MEDIUMTEXT or LONGTEXT accordingly.
所以最好使用比 TEXT更高限制的VARCHAR。如果要求超过 64K，请相应地使用 MEDIUMTEXT 或 LONGTEXT。
回答by Kai Noack
Queries against the TEXT table were always 3 times slower than those against the VARCHAR table (averages: 0.10 seconds for the VARCHAR table, 0.29 seconds for the TEXT table). The difference is 100% repeatable.
对 TEXT 表的查询总是比对 VARCHAR 表的查询慢 3 倍（平均：VARCHAR 表为 0.10 秒，TEXT 表为 0.29 秒）。差异是 100% 可重复的。
Benchmark from http://forums.mysql.com/read.php?24,105964,105964
回答by Jason
VARCHAR you can set a limit for how many chars it will accept per record, text is (virtually) unlimited... not exactly sure about performance, but i would assume a more specific datatype (varchar) would be faster.
VARCHAR 您可以设置每条记录将接受多少个字符的限制，文本（实际上）是无限的……对性能不太确定，但我认为更具体的数据类型（varchar）会更快。
回答by Fernando
VARCHAR should have a better performance since it has a limited size. In fact, in all of my experiences with MySQL, the search operation was always faster with VARCHAR than TEXT. Anyway, it's based on my experience. You should check the documentation to find out more about it.
VARCHAR 应该有更好的性能，因为它的大小有限。事实上，在我使用 MySQL 的所有经验中，使用 VARCHAR 的搜索操作总是比使用 TEXT 更快。无论如何，这是基于我的经验。您应该查看文档以了解更多信息。
回答by DisgruntledGoat
It really depends on your data type.
这实际上取决于您的数据类型。
If your field is fixed-length (e.g. a 32-character hash value), then use CHAR. This has better performance because every entry takes up the same space per row.
如果您的字段是固定长度的（例如 32 个字符的哈希值），则使用 CHAR。这具有更好的性能，因为每个条目每行占用相同的空间。
The standard limit for VARCHAR was 255 characters but I think it's been increased now. TEXT is pretty damn long and is generally only used for big content like a whole blog post, and comments if you don't want a limit.
VARCHAR 的标准限制是 255 个字符，但我认为现在已经增加了。TEXT 非常长，通常仅用于大型内容，例如整个博客文章，如果您不想要限制，则可以评论。
With regard to size there is no (or very little) difference between VARCHAR and TEXT since they just store what they need to. CHAR fields will always take up their allotted length.
关于大小，VARCHAR 和 TEXT 之间没有（或很少）差异，因为它们只存储所需的内容。CHAR 字段将始终占用其分配的长度。
Performance-wise, VARCHAR is usually faster. VARCHARs can be indexed too which leads to faster searching.
性能方面，VARCHAR 通常更快。也可以对 VARCHAR 进行索引，从而加快搜索速度。
回答by shantanuo
MySQL will internally convert TEXT to varchar while creating temporary tables. So it is better to use VARCHAR if possible. There are a few minor bugs related to TEXT column such as...
MySQL 在创建临时表时会在内部将 TEXT 转换为 varchar。因此，如果可能，最好使用 VARCHAR。有一些与 TEXT 列相关的小错误，例如...
回答by Sumeet Gohel
As per my Opinion VARCHAR is best option when u know the length of characters. It will also reduce garbage Memory Allocations and space issue. TEXT will consume 255 where as VARCHAR will consume as u give the values to it.
根据我的意见，当您知道字符长度时，VARCHAR 是最佳选择。它还将减少垃圾内存分配和空间问题。TEXT 将消耗 255，而 VARCHAR 将在您为它提供值时消耗。
As per performance, VARCHAR is also faster then TEXT.
根据性能，VARCHAR 也比 TEXT 快。
回答by Sasha
There is a subtle difference in text and varchar. I have a table as shown:
text 和 varchar 之间存在细微差别。我有一张表，如图所示：
CREATE TABLE `test`.`tbl`(
`kee` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`txt` TEXT(100),
`vrchr` VARCHAR(100),
PRIMARY KEY (`kee`)
);
I insert a row:
我插入一行：
INSERT INTO `tbl`
(`txt`,
`vrchr`)
VALUES ('1
2
3',
'1
2
3');
The column txthas value:
1
2
3
and column vrchrhas value:
1
txt列的值为：
1
2
3
，vrchr列的值为：
1
