MySQL:大型 VARCHAR 与 TEXT?

我在 MySQL 中有一个消息表,该表记录了用户之间的消息。除了典型的 ID 和消息类型(所有整数类型)之外,我还需要将实际消息文本另存为 VARCHAR 或 TEXT。我将前端限制设置为 3000 个字符,这意味着消息插入数据库的时间绝不会超过此时间。

是否有使用 VARCHAR(3000)或 TEXT 的理由?只是编写 VARCHAR(3000)有点不合常理。我曾经在 Stack Overflow 上浏览过其他类似的文章,但是最好获得特定于这种常见消息存储类型的视图。

答案

  • TEXTBLOB存储在表外,该表仅具有指向实际存储位置的指针。

  • VARCHAR与表内联存储。在大小合理的情况下, VARCHAR更快,其折衷会更快,这取决于您的数据和硬件,因此您希望使用数据对真实场景进行基准测试。

更新 VARCHAR还是TEXT是内联存储还是非记录存储取决于数据大小,列大小,row_format 和 MySQL 版本。它依赖于 “文” 与 “VARCHAR”。

您可以预测用户输入多长时间吗?

VARCHAR(X)

案例:用户名,电子邮件,国家 / 地区,主题,密码


文本

案例:消息,电子邮件,评论,格式化文本,html,代码,图像,链接


中文字

案例:大型 json 正文,中短长度的书籍,csv 字符串


长文本

案例:教科书,程序,日志文件的年限,哈利 · 波特与火焰杯,科学研究记录

只是为了阐明最佳做法:

  1. 文本格式的消息几乎应始终存储为 TEXT(它们最终会任意长)

  2. 字符串属性应存储为 VARCHAR(目标用户名,主题等)。

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

消息本身是什么使它们不能超过 3000 个字符?如果这只是一个任意的应用程序约束(例如,对于文本框之类的东西),请在数据层使用TEXT字段。

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

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

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

简短的回答:没有实用性,性能或存储差异。

长答案:

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

对于VARCHAR较小限制,相对于TEXT有一些优点。

  • “较小” 表示 191、255、512、767 或 3072 等,具体取决于版本,上下文和CHARACTER SET
  • INDEXes在可索引的列INDEXes方面受到限制。 (767 或 3072 字节 ;这取决于版本和设置)
  • 由复杂的SELECTs创建的中间表以两种不同的方式处理 - MEMORY(速度更快)或 MyISAM(速度更快)。如果涉及 “大” 列,则会自动选择较慢的技术。 (8.0 版中将进行重大更改;因此,此项目符号可能会有所更改。)
  • 与上一项相关,所有TEXT数据类型(与VARCHAR相对)都直接跳到 MyISAM。也就是说,对于生成的临时表, TINYTEXT自动比等效的VARCHAR 。 (但这将讨论引向了第三方向!)
  • VARBINARY就像VARCHAR一样; BLOB就像TEXT一样。

反驳其他答案

最初的问题是一件事(使用哪种数据类型)。接受的答案回答了其他问题(记录外存储)。该答案现在已过期。

当启动回答该线程时,InnoDB 中只有两种 “行格式”。此后不久,又引入了两种格式( DYNAMICCOMPRESSES )。

TEXTVARCHAR()的存储位置基于大小 ,而不是数据类型的名称 。有关大文本 / blob 列的开 / 关记录存储的最新讨论,请参见this

前面的答案在主要问题上的坚持不够:即使在非常简单的查询中,例如

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

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

来源(带有度量): https : //nicj.net/mysql-text-vs-varchar-performance/ (这是指 “标准”(?)MyISAM 存储引擎中TEXT vs VARCHAR的处理。其他,例如 InnoDB。)

Varchar 用于小数据,例如电子邮件地址,而 Text 用于大数据,例如新闻文章,Blob 用于二进制数据,例如图像。

Varchar 的性能更强大,因为它完全依靠内存运行,但是如果数据太大varchar(4000)例如varchar(4000) ,则情况并非如此。

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

Blob 慢得多,因此仅当您没有太多数据(例如 10000 张图像,将花费 10000 条记录)时才使用它。

请遵循以下提示以实现最大速度和性能:

  1. 使用 varchar 作为名称,标题,电子邮件

  2. 使用文本处理大数据

  3. 在不同表格中分开文字

  4. 在 ID(例如电话号码)上使用左联接查询

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

这将使表在数据大于 10 M 且保证最大大小为 10GB 的表上花费毫秒的时间。

VARCHAR 和 TEXT 之间存在巨大差异。虽然可以为 VARCHAR 字段建立索引,但不能为 TEXT 字段建立索引。 VARCHAR 类型字段是内联存储的,而 TEXT 是脱机存储的,实际上只有 TEXT 数据的指针存储在记录中。

如果必须索引字段以进行快速搜索,更新或删除,而不是使用 VARCHAR(无论大小如何)。 VARCHAR(10000000)永远不会与 TEXT 字段相同,因为这两种数据类型本质上是不同的。

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

而不是选择 TEXT。