我在MySQL中有一个消息表,用于在用户之间记录消息 . 除了典型的id和消息类型(所有整数类型),我需要将实际的消息文本保存为VARCHAR或TEXT . 我设置的前端限制为3000个字符,这意味着消息永远不会插入到数据库中,因为它比这更长 .
是否有理由使用VARCHAR(3000)或TEXT?有一些关于编写VARCHAR(3000)的东西感觉有些违反直觉 . 我已经浏览了Stack Overflow上的其他类似帖子,但是很高兴获得特定于此类公共消息存储的视图 .
你能预测用户输入的时间吗?
VARCHAR(X)案例:用户名,电子邮件,国家,主题,密码TEXT案例:消息,电子邮件,评论,格式化文本,html,代码,图像,链接MEDIUMTEXT案例:大型json机构,短到中等长度的书籍,csv字符串LONGTEXT案例:教科书,程序,多年的日志文件,哈利波特和火焰杯,科研记录
免责声明:我不是MySQL专家......但这是我对这些问题的理解 .
我认为TEXT存储在mysql行之外,而我认为VARCHAR存储为行的一部分 . mysql行有一个最大行长度 . 因此,您可以使用VARCHAR限制可以存储在一行中的其他数据量 .
同样由于VARCHAR构成了行的一部分,我怀疑查看该字段的查询将比使用TEXT块的查询稍快一些 .
前面的答案并不足以解决主要问题:即使在非常简单的查询中也是如此
(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字节 . 这样的临时表不能在内存中处理并写入磁盘 . 预计这种影响将是灾难性的 .
VARCHAR
CHAR
VARCHAR(65000)
来源(带指标):https://nicj.net/mysql-text-vs-varchar-performance/(这是指在"standard"(?)MyISAM存储引擎中处理 TEXT vs VARCHAR . 在其他情况下可能会有所不同,例如InnoDB . )
TEXT
TEXT 和 BLOB 存储在表格之外,表格只有一个指向实际存储位置的指针 .
BLOB
VARCHAR 与表格内联存储 . 当大小合理时, VARCHAR 更快,其权衡速度更快取决于您的数据和硬件,您需要使用数据对真实世界场景进行基准测试 .
Update VARCHAR 或 TEXT 是以内联方式存储还是以非记录方式存储取决于数据大小,列大小,row_format和MySQL版本 . 它不依赖于"text" vs "varchar" .
只是为了澄清最佳做法:
文本格式消息几乎总是存储为TEXT(它们最终是任意长的)
字符串属性应存储为VARCHAR(目标用户名,主题等) .
我知道你有一个前端限制,这是非常好的,直到它没有 . * grin *诀窍是将DB视为与连接到它的应用程序分开 . 仅仅因为一个应用程序限制数据,并不意味着数据本质上受限制 .
什么是消息本身迫使他们永远不会超过3000个字符?如果它只是一个任意的应用程序约束(例如,对于文本框或其他东西),请在数据层使用 TEXT 字段 .
Short answer: 没有实际,性能或存储,差异 .
Long answer:
VARCHAR(3000) (或任何其他大限制)与 TEXT 之间基本没有区别(在MySQL中) . 前者将截断为3000个字符;后者将截断为65535字节 . (我区分字节和字符,因为一个字符可以占用多个字节 . )
VARCHAR(3000)
对于 VARCHAR 中的较小限制,与 TEXT 相比有一些优势 .
"smaller"表示191,255,512,767或3072等,具体取决于版本,上下文和 CHARACTER SET .
CHARACTER SET
INDEXes 受限于列的索引大小 . (767或3072字节;这取决于版本和设置)
INDEXes
由复杂 SELECTs 创建的中间表以两种不同的方式处理 - MEMORY(更快)或MyISAM(更慢) . 当涉及'large'列时,会自动选择较慢的技术 . (版本8.0中会有重大更改;因此此项目可能会发生变化 . )
SELECTs
与上一项相关,所有 TEXT 数据类型(与 VARCHAR 相反)直接跳转到MyISAM . 也就是说,生成的临时表的 TINYTEXT 自动比等效的 VARCHAR 更差 . (但这需要在第三个方向进行讨论!)
TINYTEXT
VARBINARY 就像 VARCHAR ; BLOB 就像 TEXT .
VARBINARY
Rebuttal to other answers
原始问题问了一件事(使用哪种数据类型);接受的答案回答了别的问题(非记录存储) . 那个答案现在已经过时了 .
当这个线程启动并回答时,InnoDB中只有两个"row formats" . 不久之后,又推出了两种格式( DYNAMIC 和 COMPRESSES ) .
DYNAMIC
COMPRESSES
TEXT 和 VARCHAR() 的存储位置基于大小,而不是基于数据类型的名称 . 有关大型文本/ blob列的开/关记录存储的讨论,请参阅this .
VARCHAR()
6 回答
你能预测用户输入的时间吗?
免责声明:我不是MySQL专家......但这是我对这些问题的理解 .
我认为TEXT存储在mysql行之外,而我认为VARCHAR存储为行的一部分 . mysql行有一个最大行长度 . 因此,您可以使用VARCHAR限制可以存储在一行中的其他数据量 .
同样由于VARCHAR构成了行的一部分,我怀疑查看该字段的查询将比使用TEXT块的查询稍快一些 .
前面的答案并不足以解决主要问题:即使在非常简单的查询中也是如此
可能需要临时表,如果涉及
VARCHAR
字段,则会将其转换为临时表中的CHAR
字段 . 因此,如果您在表中有一个带有VARCHAR(65000)
字段的500 000行,则此列仅使用6.5 * 5 * 10 ^ 9字节 . 这样的临时表不能在内存中处理并写入磁盘 . 预计这种影响将是灾难性的 .来源(带指标):https://nicj.net/mysql-text-vs-varchar-performance/(这是指在"standard"(?)MyISAM存储引擎中处理
TEXT
vsVARCHAR
. 在其他情况下可能会有所不同,例如InnoDB . )TEXT
和BLOB
存储在表格之外,表格只有一个指向实际存储位置的指针 .VARCHAR
与表格内联存储 . 当大小合理时,VARCHAR
更快,其权衡速度更快取决于您的数据和硬件,您需要使用数据对真实世界场景进行基准测试 .Update
VARCHAR
或TEXT
是以内联方式存储还是以非记录方式存储取决于数据大小,列大小,row_format和MySQL版本 . 它不依赖于"text" vs "varchar" .只是为了澄清最佳做法:
文本格式消息几乎总是存储为TEXT(它们最终是任意长的)
字符串属性应存储为VARCHAR(目标用户名,主题等) .
我知道你有一个前端限制,这是非常好的,直到它没有 . * grin *诀窍是将DB视为与连接到它的应用程序分开 . 仅仅因为一个应用程序限制数据,并不意味着数据本质上受限制 .
什么是消息本身迫使他们永远不会超过3000个字符?如果它只是一个任意的应用程序约束(例如,对于文本框或其他东西),请在数据层使用
TEXT
字段 .Short answer: 没有实际,性能或存储,差异 .
Long answer:
VARCHAR(3000)
(或任何其他大限制)与TEXT
之间基本没有区别(在MySQL中) . 前者将截断为3000个字符;后者将截断为65535字节 . (我区分字节和字符,因为一个字符可以占用多个字节 . )对于
VARCHAR
中的较小限制,与TEXT
相比有一些优势 ."smaller"表示191,255,512,767或3072等,具体取决于版本,上下文和
CHARACTER SET
.INDEXes
受限于列的索引大小 . (767或3072字节;这取决于版本和设置)由复杂
SELECTs
创建的中间表以两种不同的方式处理 - MEMORY(更快)或MyISAM(更慢) . 当涉及'large'列时,会自动选择较慢的技术 . (版本8.0中会有重大更改;因此此项目可能会发生变化 . )与上一项相关,所有
TEXT
数据类型(与VARCHAR
相反)直接跳转到MyISAM . 也就是说,生成的临时表的TINYTEXT
自动比等效的VARCHAR
更差 . (但这需要在第三个方向进行讨论!)VARBINARY
就像VARCHAR
;BLOB
就像TEXT
.Rebuttal to other answers
原始问题问了一件事(使用哪种数据类型);接受的答案回答了别的问题(非记录存储) . 那个答案现在已经过时了 .
当这个线程启动并回答时,InnoDB中只有两个"row formats" . 不久之后,又推出了两种格式(
DYNAMIC
和COMPRESSES
) .TEXT
和VARCHAR()
的存储位置基于大小,而不是基于数据类型的名称 . 有关大型文本/ blob列的开/关记录存储的讨论,请参阅this .