MySQL 我应该使用哪种列数据类型来存储大量文本或 html
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5458376/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What column data type should I use for storing large amounts of text or html
提问by user339108
I have a column in a table which used to be varchar(255) in the beginning and due to some design changes now it is varchar(1536) = 1024 + 512. I will not be searching or indexing this field, does it make sense to store this value in a different data type other than a varchar if you would like to optimize this for performance?
我在一个表中有一个列，它在开始时曾经是 varchar(255)，由于一些设计更改，现在它是 varchar(1536) = 1024 + 512。我不会搜索或索引这个字段，这是否有意义如果您想优化此值以提高性能，是否将此值存储在 varchar 以外的不同数据类型中？
采纳答案by Knowledge Craving
Yes, it will be better if you can store the values in the "TEXT" data type. For more details, please read this article.
是的，如果您可以将值存储在“ TEXT”数据类型中会更好。有关更多详细信息，请阅读这篇文章。
Regarding knowledge of storage requirements, you can read this one.
关于存储要求的知识，你可以阅读这个。
Hope it helps.
希望能帮助到你。
回答by vbence
You should use TEXTlike the others said, but there is some important advice every time you use TEXT or BLOB: decouple them form your base tableas they really slow down accessing the table. Imagine the following structure:
您应该TEXT像其他人说的那样使用，但是每次使用 TEXT 或 BLOB 时都有一些重要的建议：将它们与基表分离，因为它们确实会减慢访问表的速度。想象一下以下结构：
CREATE TABLE article (
id INT(10) UNSIGNED,
title VARCHAR(40),
author_id INT(10) UNSIGNED,
created DATETIME,
modified DATETIME
);
CREATE TABLE article_body (
id INT(10) UNSIGNED,
body TEXT
);
Whenever you list articles you can use the articletable (last 5 articles of author 33):
每当您列出文章时，您都可以使用该article表格（作者 33 的最后 5 篇文章）：
SELECT id, title FROM article WHERE author_id=33 ORDER BY created DESC LIMIT 5
And when someone really opens the article you can use something like:
当有人真正打开文章时，您可以使用以下内容：
SELECT a.title, ab.body
FROM article AS a
LEFT JOIN article_body AS ab ON ab.id = a.id
WHERE a.id=82
回答by Isotopp
You should be using a file, not a database to store this. Especially not MySQL. I made a writeup once explaining what happens if you for example download images out of a database BLOB, see http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html. Using files, you can use the web server fast path using the sendfile(2) system call, and it is much faster to use this.
您应该使用文件而不是数据库来存储它。尤其不是 MySQL。我曾经写过一篇文章，解释了例如从数据库 BLOB 下载图像时会发生什么，请参阅http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.html。使用文件，您可以使用 sendfile(2) 系统调用来使用 Web 服务器快速路径，并且使用它要快得多。
MySQL also has no BLOB API. That means, it is impossible to upload or download objects larger than max_allowed_packet, and it is hard to work your way around that using SUBSTRING(), because that will make needless copies of strings in server memory.
MySQL 也没有 BLOB API。这意味着，不可能上传或下载大于 max_allowed_packet 的对象，并且很难使用 SUBSTRING() 解决这个问题，因为这会在服务器内存中生成不必要的字符串副本。
If you absolutely MUST store BLOB or TEXT data in the server, you have the choice of TINYTEXT, TEXT, MEDIUMTEXT and LARGETEXT which are limited to 255, 65535, 16 MB and 4GB of data in the server, additionally constrained by max_allowed_packet.
如果您绝对必须在服务器中存储 BLOB 或 TEXT 数据，您可以选择 TINYTEXT、TEXT、MEDIUMTEXT 和 LARGETEXT，它们被限制为服务器中的 255、65535、16 MB 和 4GB 数据，另外还受 max_allowed_packet 的限制。
Large BLOB or TEXT information will completely wreck data density in your table. It is useful to create an artificial 1:1 or 1:0 relationship to a BLOB table, and then store the blobs in this extra table.
大的 BLOB 或 TEXT 信息将完全破坏表中的数据密度。创建与 BLOB 表的人工 1:1 或 1:0 关系，然后将这些 blob 存储在这个额外的表中是很有用的。
When MySQL shows a query plan that is 'using tempoary', it means that the server needs to materialize the result set table in the server before delivering the result. This is being done using MEMORY tables, if possible. Any TEXT or BLOB type cannot be represented in MEMORY tables, hence the temporary table then hits the disk as a MyISAM table instead.
当MySQL显示'using tempoary'的查询计划时，意味着服务器需要在交付结果之前在服务器中物化结果集表。如果可能，这是使用 MEMORY 表完成的。任何 TEXT 或 BLOB 类型都不能在 MEMORY 表中表示，因此临时表然后作为 MyISAM 表访问磁盘。
You need to scan for such query plans, and convert them into something that loads the ID values of the BLOB/TEXT values instead. In a second query, you'd then SELECT id, thetext FROM texttable WHERE id in ( ... ) to get the TEXT/BLOB values. That will make the query with 'using temporary' not use TEXT or BLOB types, and you can get the TEXT fields then with a trivial query that runs without 'using temporary'.
您需要扫描此类查询计划，并将它们转换为加载 BLOB/TEXT 值的 ID 值的内容。在第二个查询中，您然后 SELECT id, thetext FROM texttable WHERE id in ( ... ) 以获取 TEXT/BLOB 值。这将使使用“使用临时”的查询不使用 TEXT 或 BLOB 类型，然后您可以使用不使用“使用临时”的简单查询来获取 TEXT 字段。
You can learn more about the internals of MySQL TEXT and BLOB storage by reading http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/
您可以通过阅读http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/了解有关 MySQL TEXT 和 BLOB 存储内部结构的更多信息
回答by Femaref
I would use textfor columns with variable length.
我将text用于长度可变的列。
