使用文本或VARCHAR哪个数据类型更好?

This question is based on two things performance and size

这个问题是基于两件事的表现和大小。

Which DATATYPE is better to use TEXT or VARCHAR? Based on performance which will affect and which will impove?

哪种数据类型最好使用文本或VARCHAR?基于将会影响和影响的性能?

9 个解决方案

#1

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 VARCHAR because 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，因为它会更高效一点。如果你需要更多的空间，选择文本。如果你不确定文本会占用多少空间，你应该选择文本;性能差异不是很大，最好是避免将来发生的情况，而不是在需求发生变化时冒险更改。只是我两美分。

In the comments, Pitarou points out that, if MySQL creates a temporary table for your query (see this), TEXT columns 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为您的查询创建了一个临时表(请参阅这个)，文本列将不会存储在内存中，并且必须从磁盘中读取，这要慢得多。(来源，页面底部)不过，对于大多数查询来说，这并不重要。

In case anyone was wondering how PostgreSQL compares, I found this benchmark that 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，你用的是什么类型并不重要。

#2

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.)

从v5.0.3开始，VARCHAR的限制从0-256增加到0-65,535(取决于最大行大小(在所有列之间共享的65,535字节)和所使用的字符集)。

Ref. http://dev.mysql.com/doc/refman/5.0/en/char.html

引用http://dev.mysql.com/doc/refman/5.0/en/char.html

If you are using TEXT that is fixed 64k length, even if you required lesser limit

如果您使用的文本是固定的64k长度，即使您需要较小的限制

So Better to go with VARCHAR with higher limit than TEXT. If requirement is more than 64K go with MEDIUMTEXT or LONGTEXT accordingly.

所以最好选择比文本高的VARCHAR。如果要求大于64K，则使用MEDIUMTEXT或LONGTEXT。

#3

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.

对文本表的查询总是比对VARCHAR表的查询慢3倍(平均:VARCHAR表的0.10秒，文本表的0.29秒)。差异是100%可重复的。

Benchmark from http://forums.mysql.com/read.php?24,105964,105964

基准从http://forums.mysql.com/read.php?24,105964年、105964年

#4

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)会更快。

#5

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的搜索操作总是比文本快。不管怎样，这是基于我的经验。您应该检查文档以了解更多关于它的信息。

#6

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个字符，但我认为现在已经增加了。文本非常长，通常只用于大型内容，比如一篇博客文章，如果你不想要限制的话，还可以评论。

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和文本之间没有(或很少)差异，因为它们只存储需要的内容。CHAR字段总是会占用分配的长度。

Performance-wise, VARCHAR is usually faster. VARCHARs can be indexed too which leads to faster searching.

在性能方面，VARCHAR通常更快。VARCHARs也可以被编入索引，从而导致更快的搜索。

#7

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将在创建临时表时在内部将文本转换为varchar。所以如果可能的话最好使用VARCHAR。有一些与文本列相关的小错误，比如……

http://bugs.mysql.com/bug.php?id=36676

#8

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是最好的选择。它还将减少垃圾内存分配和空间问题。文本将消耗255,VARCHAR将消耗，因为你给它的值。

As per performance, VARCHAR is also faster then TEXT.

就性能而言，VARCHAR比文本更快。

#9

-5

There is a subtle difference in text and varchar. I have a table as shown:

文本和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 txt has value:
1
2
3
and column vrchr has value:
1

列txt的值为:1 2 3，列vrchr的值为:1

#1

#2