MySQL：大的VARCHAR与TEXT之间的区别？

Question

我在MySQL中有一个消息表，记录用户之间的信息。除了典型的id和消息类型（所有整数类型）之外，我需要将实际消息文本保存为VARCHAR或TEXT。我在前端设置了3000个字符的限制，这意味着消息永远不会以超过这个长度插入到数据库中。

对于选择VARCHAR(3000)或TEXT是否有理由？仅写VARCHAR(3000)感觉有些违反直觉。我已经查看了Stack Overflow上其他类似的帖子，但希望能够得到针对此类常见消息存储的具体观点。

- Tom

---

虽然这是一篇有点旧的内容，但我来这里是因为遇到了一个问题，让我开始思考这个话题。在我的情况下，我的前端表单被限制在了 2,000 个字符，但是我的存储方法中的编码将国际字符编码为多个字符（每个字符可能会变成3-12个字符）。因此，我的 2,000 个字符突然变成了高达 24,000 个字符。这是需要考虑的一些事情... - James S

对于许多并发插入操作，我发现文本的速度要快得多。 - Ray S.

@JamesS：utf8mb4... >.< - indivisible

请考虑发布一份更新后的答案，而不是关闭问题。 - user3956566

@YvetteColomb - 我添加了一个答案。我主要想摆脱已经过时的采纳答案。我来到问答中心是因为有人引用了不正确的信息，说“754个赞，所以一定是对的”。我编辑了采纳的答案，也编辑了其他答案（虽然这感觉不太妥当）。 - Rick James

Answer 1

• TEXT 和 BLOB 可能会被存储在表外，而表只有指向实际存储位置的指针。它存储的位置取决于许多因素，如数据大小、列大小、行格式和 MySQL 版本。

• VARCHAR 存储在表中。当大小合理时，VARCHAR 更快，其速度取决于您的数据和硬件，您需要使用您的数据对真实情况进行基准测试。

- MindStalker

---

如果频繁检索数据（也就是大部分查询都需要该数据），那么将VARCHAR（内联存储）存储通常会更快。然而，如果有大量数据不常被查询（也就是没有任何查询引用该数据），则最好不要将数据存储为内联存储方式。对于内联存储的数据，行大小也存在一个上限。 - spencer7593

避免使用“内联”存储的确切好处是可以在一个块中存储更多的行，这意味着表行占用了InnoDB缓存中更少的块（较小的内存占用），并且意味着要传输到磁盘的块更少（减少I/O）。但是，只有当“离行”存储的列在查询中很少被引用时，才会产生性能上的好处。如果大多数查询都引用了“离行”列，那么这个好处基本上会消失。如果这些列适合于最大行大小，并且经常被引用，则首选内联。 - spencer7593

"VARCHAR在大小适中的情况下速度更快"。那么什么是“适中”的字符数？是100个？1000个？100,000个？ - tim peterson

对于InnoDB引擎，这个答案是不正确的。如果在给定行上，VARCHAR和BLOB/TEXT的值适合页面大小（16KB，每个页面必须至少包含两行），它们都将与其他列一起存储。如果字符串太大而无法适应，它会溢出到其他页面。详细解释请参见http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/。 - Bill Karwin

Answer 2

你能预测用户输入的长度吗？

VARCHAR(X)

最大长度: 可变长度，最多65,535字节（64KB）
应用场景: 用户名、电子邮件、国家、主题、密码

---

TEXT

最大长度: 65,535字节（64KB）
应用场景: 消息、邮件、评论、格式化文本、HTML、代码、图像、链接

---

MEDIUMTEXT

最大长度: 16,777,215字节（16MB）
应用场景: 大型JSON体、中短篇小说、CSV字符串

---

LONGTEXT

最大长度: 4,294,967,29字节（4GB）
应用场景: 教科书、程序、多年的日志文件、哈利波特与火焰杯、科学研究记录

更多信息请参考此问题。

- Michael J. Calkins

---

可预测性实际上只是一个次要因素。决定因素应该是最大预期长度。你提到的更具可预测性的项目之所以如此，仅因为它们比其他项目更短。 - Andrew Barber

这就是我的意思。所有其他帖子都很好地解释了这两者之间的区别，但并未涉及实际需要在两者之间做出选择的情况。我试图指出，对于可预测的短文本，使用varchar是一个不错的选择，而对于任意长度的文本，使用text是一个不错的选择。 - Michael J. Calkins

Answer 3

为了阐明最佳实践：

1. 文本格式的消息应几乎总是以TEXT形式存储（它们最终会变得任意长）

2. 字符串属性应该存储为VARCHAR（目标用户名、主题等）。

我知道你有前端限制，这很好，直到不好为止。 * 嘿嘿 * 诀窍是将DB视为与连接到它的应用程序分离的内容。 只是因为一个应用程序对数据设置了限制，并不意味着数据在本质上有限制。

是什么让消息本身永远不能超过3000个字符？ 如果只是任意应用程序约束（比如文本框之类的），请在数据层使用TEXT字段。

- James

---

那么，您会建议如何处理像magnet: URL一样的超大URL？它们可能比255个字符长得多。 - Roland

Answer 4

简短回答：在实际应用中，VARCHAR(3000)（或其他任何大的限制）和TEXT没有实际性能或存储上的差异。

详细回答：

在MySQL中，VARCHAR(3000)（或任何其他大的限制）和TEXT基本上没有区别。前者会截断到3000个字符；后者会截断到65535个字节。（我之所以区分字节和字符是因为一个字符可能占据多个字节。）

对于VARCHAR中较小的限制，有一些优点：

• “较小”意味着191、255、512、767或3072，取决于版本、上下文和CHARACTER SET。
• INDEXes在索引列的大小方面受到限制。（767或3072个字节；这取决于版本和设置）
• 由复杂的SELECTs创建的中间表有两种不同的处理方式——MEMORY（更快）或MyISAM（更慢）。当涉及到“大”列时，会自动选择较慢的技术。（在版本8.0中有重大变化，因此此项要点可能会发生变化。）
• 与前一项相关的是，所有TEXT数据类型（与VARCHAR相对）都直接跳转到MyISAM。也就是说，TINYTEXT自动生成的临时表比等效的VARCHAR更差。（但这将带我们进入第三个方向的讨论！）

VARBINARY类似于VARCHAR；BLOB类似于TEXT。

有几个'大型'VARCHAR的表可能会达到整个表定义的64KB限制；切换到TEXT是一个简单而实用的解决方法。（例如：(42000) Row size too large, from an Oracle dump to a MySQL dump）

对其他答案的反驳

原问题询问了一件事（使用哪种数据类型）；被接受的答案回答了另一件事（离线存储）。那个答案现在已经过时了。

当这个线程被创建和回答的时候，InnoDB中只有两种“行格式”。不久之后，引入了另外两种格式（DYNAMIC和COMPRESSED）。

TEXT和VARCHAR()的存储位置是基于大小，而不是数据类型名称。有关大文本/ blob列的在线/离线记录存储的更新讨论，请参见此处。

- Rick James

---

@KostaKontos - 感谢您的赞扬和纠错。即使是在8年和800个赞之后，如果我看到需要更好的答案，我也会添加一个答案。 - Rick James

Answer 5

免责声明：我不是MySQL专家……但这是我的理解。

我认为TEXT类型的数据存储在mysql行外，而VARCHAR类型的数据则作为行的一部分存储。MySQL行有一个最大长度限制……因此，您可以通过使用VARCHAR来限制在行中存储的其他数据量。

另外，由于VARCHAR作为行的一部分而存在，我怀疑查询该字段的速度会比使用TEXT块的查询略快。

- Michael Anderson

---

行长度限制为65,535字节[http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html]。 如果您的列是utf8编码，那意味着一个3000字符的varchar列最多可占用9000个字节。 - Jan Fabry

UTF-8字符最多可以达到4个字节，因此我认为您的意思是12,000字节（除非这里涉及到一些我不了解的MySQL事情）。 - raylu

MySQL的UTF-8是“伪UTF-8”，最多只支持每个字符3个字节，因此在MySQL的UTF-8中没有直接存储BMP平面之外的Unicode字符的方法。这在MySQL 5.5中得到了修复。 - Pacerier

我认为这个断言仅适用于MyISAM。我找不到确定的来源，但我相信InnoDB也会将TEXT内联存储在表中。 - dotancohen

@dotancohen 我在这里找到了一份资料，解释了使用InnoDB存储可变长度数据的方式可能会有所不同（可以在行内或外部存储）。http://mysqlserverteam.com/externally-stored-fields-in-innodb/ - KiX Ortillan

Answer 6

前面的答案没有足够强调主要问题：即使在非常简单的查询中，例如：

(SELECT t2.* FROM t1, t2 WHERE t2.id = t1.id ORDER BY t1.id) 

如果需要临时表，而且涉及到一个VARCHAR字段，则该字段转换为临时表中的CHAR字段。因此，如果您的表中有一个带有VARCHAR(65000)字段的500,000行，光是这一列就会使用6.5*5*10^9字节。这样的临时表无法在内存中处理，必须写入磁盘。可以预料到其影响将是灾难性的。

来源（带度量单位）：https://nicj.net/mysql-text-vs-varchar-performance/（这是指“标准” (?) MyISAM存储引擎中处理TEXT与VARCHAR的情况。 在其他存储引擎中可能不同，例如InnoDB。）

- Max

---

从版本5.7到8.0，InnoDB中的varchar临时表变成了可变长度。 - Rick James

Answer 7

Varchar用于存储小型数据，例如电子邮件地址，而Text用于存储更大的数据，例如新闻文章，Blob用于存储二进制数据，例如图片。

Varchar的性能更强，因为它完全依赖内存运行。但是如果数据太大，比如varchar(4000)，那么情况就不同了。

另一方面，Text不会固定在内存中，受到磁盘性能的影响，但您可以通过将文本数据分离到单独的表中并应用左连接查询来检索文本数据。

Blob速度较慢，只有在没有太多数据（例如10000张图像）时才使用Blob。

遵循以下提示以获得最大速度和性能：

1. 使用varchar来存储名称、标题、电子邮件

2. 使用Text存储大数据

3. 将文本分开存储在不同的表中

4. 在ID（如电话号码）上使用Left Join查询

5. 如果要使用Blob，请应用与Text相同的提示

这将确保对数据量>10 M且大小高达10GB的表进行查询的时间不超过毫秒。

- Creative87

Answer 8

VARCHAR和TEXT之间有着巨大的区别。VARCHAR字段可以索引，而TEXT字段则不能。VARCHAR类型的字段存储在行内，而TEXT存储在离线，实际上只是存储了指向TEXT数据的指针。

如果您需要为更快的搜索、更新或删除而索引您的字段，请选择VARCHAR，无论大小如何。VARCHAR(10000000)永远不会与TEXT字段相同，因为这两种数据类型在本质上是不同的。

• 如果您仅将字段用于归档
• 您不关心数据检索速度
• 您关心速度，但将在搜索查询中使用'%LIKE%'运算符，因此索引不会有太大帮助
• 您无法预测数据长度的限制

那么请选择TEXT。

- Viktor Joras

---

部分误导性信息：TEXT列不能完全索引。当您在索引中包含TEXT列时，必须指定长度。此外，对于VARCHAR> 255的情况下，也不能将其全部索引，因为索引大小存在最大长度限制。 - eRadical

Answer 9

对于这里的许多答案，我需要进行一些更正，即使现在可能已经有点晚了。

根据MySQL的文档，文本字段可以完全被索引。

链接提供 https://dev.mysql.com/doc/refman/5.6/en/column-indexes.html

总体上来说，Varchar字段比Text字段写入时间更长，但只有在有大量写请求时才会有影响。

- Yuriy Semenikhin