MySQL：大型VARCHAR与文本？

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

使用VARCHAR（3000）或TEXT是否有道理？仅仅编写VARCHAR（3000）有点违反直觉。我已经在Stack Overflow上阅读了其他类似的帖子，但最好获得特定于这种类型的常见消息存储的视图。

510684 次浏览
小开
最佳答案

  • TEXT和BLOB可以通过表外存储，表只有一个指向实际存储位置的指针。它存储在哪里取决于很多东西，比如数据大小、列大小、row_format和MySQL版本。

  • 当大小合理时，VARCHAR会更快，其中的权衡会更快，这取决于您的数据和硬件，您希望使用您的数据对真实世界的场景进行基准测试。

小开

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

我认为TEXT存储在mysql行之外，而我认为VARCHAR存储在行的一部分。mysql行有最大行长度…因此您可以使用VARCHAR限制可以在一行中存储的其他数据量。

同样由于VARCHAR构成了行的一部分，我怀疑查看该字段的查询会比使用TEXT块的查询稍微快一些。

小开

只是为了澄清最佳实践：

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

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

我知道你有前端限制，这很好，直到它没有。*咧嘴笑*诀窍是将数据库与连接到它的应用程序分开。仅仅因为一个应用程序对数据进行了限制，并不意味着数据本质上是有限的。

消息本身是什么迫使它们永远不会超过3000个字符？如果它只是一个任意的应用程序约束（例如，对于输入框或其他东西），请在数据层使用TEXT字段。

小开

你能预测用户输入的时间有多长吗？

VARCHAR（X）

最大长度：变量，最大65,535字节（64KB）
case：用户名、电子邮件、国家、主题、密码

文本

最大长度： 65,535字节（64KB）
案例：消息、电子邮件、评论、格式化文本、html、代码、图像、链接

介质文本

最大长度： 16,777,215字节（16MB）
case：大json主体，短到中等长度的书，csv字符串

全文

最大长度： 4,294,967,29字节（4GB）
案例：教科书，程序，多年的日志文件，哈利波特与火焰杯，科学研究日志

更多关于这个问题的信息。

小开

简短的回答：没有实际，性能或存储，差异。

很长的回答：

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

对于VARCHAR中较小的限制，与TEXT相比有一些优势。

  • “更小”意味着191、255、512、767或3072等，具体取决于版本、上下文和CHARACTER SET。
  • INDEXes限制了可以索引的列的大小。（767或3072字节；这取决于版本和设置）
  • 由复杂SELECTs创建的中间表以两种不同的方式处理——MEmorY（更快）或MyISAM（更慢）。当涉及“大”列时，会自动选择较慢的技术。（8.0版中会有重大变化；因此此项目符号项可能会发生变化。）
  • 与前一项相关，所有TEXT数据类型（与VARCHAR相反）都直接跳转到MyISAM。也就是说，对于生成的临时表，TINYTEXT自动比等效的VARCHAR更糟糕。（但这将讨论带到了第三个方向！）
  • VARBINARY就像VARCHAR；BLOB就像TEXT。
  • 具有多个“大”VARCHARs的表可能会达到整个表定义的64KB限制；切换到TEXT是一个简单实用的解决方案。（示例：（42000）行大小太大，从Oracle转储到MySQL转储）

对其他答案的反驳

最初的问题问了一件事（使用哪种数据类型）；被接受的答案回答了其他问题（非记录存储）。那个答案现在已经过时了。

当这个线程启动时和回答，InnoDB中只有两种“行格式”。不久之后，又引入了两种格式（DYNAMIC和COMPRESSED）。

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

小开

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

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

可能需要一个临时表，如果涉及VARCHAR字段，它会在临时表中转换为CHAR字段。因此，如果您的表中有500,000行带有VARCHAR(65000)字段，则仅此列将使用6.5*5*10^9字节。此类临时表无法在内存中处理并写入磁盘。预计影响将是灾难性的。

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

小开

Varchar用于电子邮件地址等小数据，而Text用于新闻文章等大得多的数据，Blob用于图像等二进制数据。

Varchar的性能更强大，因为它完全从内存运行，但如果数据太大，例如varchar(4000)，情况就不会如此。

另一方面，文本不会粘在内存上，并且会受到磁盘性能的影响，但您可以通过在单独的表中分离文本数据并应用左连接查询来检索文本数据来避免这种情况。

Blob的速度要慢得多，因此只有在您没有像10000个图像这样的大量数据时才使用它，这将花费10000条记录。

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

  1. 使用varchar作为姓名、头衔、电子邮件

  2. 将Text用于大数据

  3. 不同表格中的单独文本

  4. 对ID（如电话号码）使用左连接查询

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

这将使查询在数据>10 M且保证大小高达10GB的表上花费毫秒。

小开

VARCHAR和TEXT之间有巨大的区别。虽然VARCHAR字段可以被索引，但TEXT字段不能。VARCHAR类型字段内联存储，而TEXT离线存储，只有指向TEXT数据的指针实际存储在记录中。

如果您必须索引您的字段以更快地搜索、更新或删除，而不是使用VARCHAR，无论多大。VARCHAR（10000000）永远不会与TEXT字段相同，因为这两种数据类型本质上是不同的。

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

而不是文本。

小开

只是对这里这么多答案的修正，即使派对有点晚。

文本字段可以由MySQL根据其留档完全索引。

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

总的来说，Varchar字段写入文本字段的时间更长，但只有在您有大量写入请求时才重要


提交答案