MySQL varchar(255) vs tinytext/tinyblob 和 varchar(65535) vs blob/text
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7755629/
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
varchar(255) vs tinytext/tinyblob and varchar(65535) vs blob/text
提问by Tech4Wilco
By definition:
根据定义：
VARCHAR: The range of Length is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+1 bytes
TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters x+1 bytes
VARCHAR：Length 的范围是 1 到 255 个字符。VARCHAR 值以不区分大小写的方式排序和比较，除非给出 BINARY 关键字。x+1 字节
TINYBLOB、TINYTEXT：最大长度为 255 (2^8 - 1) 个字符的 BLOB 或 TEXT 列 x+1 字节
So based on this, I creaate the following table:
因此，基于此，我创建了下表：
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255),
`lastname` tinytext,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Or is it better to create a varchar or tinytext and why?
或者创建 varchar 或 tinytext 更好，为什么？
Is it the same for:
是否相同：
VARCHAR: The range of Length is > 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+2 bytes
BLOB, TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters x+2 bytes
VARCHAR：Length 的范围是 > 255 个字符。VARCHAR 值以不区分大小写的方式排序和比较，除非给出 BINARY 关键字。x+2 字节
BLOB、TEXT 最大长度为 65535 (2^16 - 1) 个字符的 BLOB 或 TEXT 列 x+2 字节
回答by Book Of Zeus
FROM: http://www.pythian.com/news/7129/text-vs-varchar/
来自：http: //www.pythian.com/news/7129/text-vs-varchar/
On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between the way TEXT fields and VARCHAR fields work, which are important to take into consideration.
StandardVARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.
StorageTEXT data types are stored as separate objects from the tables and result sets that contain them. This storage is transparent — there is no difference in how a query involving a TEXT field is written versus one involving a VARCHAR field. Since TEXT is not stored as part of a row, retrieval of TEXT fields requires extra [edited 1/22] memory overhead.
Maximum VARCHAR lengthThe maximum row length of a VARCHAR is restricted by the maximum row length of a table. This is 65,535 bytes for most storage engines (NDB has a different maximum row value). Theoretically the maximum length of a VARCHAR is 65,536 bytes. Overhead further limits the actual maximum size of a VARCHAR.
Storing the length of a VARCHAR field takes 1 byte if the VARCHAR field has a maximum length of 0-255 bytes; if it is greater than 255 bytes, the overhead to store the length is 2 bytes. If the VARCHAR field allows NULL values, that adds additional overhead — every table uses 1 byte of overhead for each set of 8 fields that allow NULL values. If the VARCHAR is the only row in the table, and does not allow NULL values, the maximum length allowed for VARCHAR is 65,532 bytes.
Keep in mind that that the number in VARCHAR(x) represents number of characters, not number of bytes. Therefore, you may have difficulties trying to define a table with only VARCHAR(65532) if the character set uses multi-byte characters, such as UTF-8.
If you attempt to define a VARCHAR value that is longer than allowed, you will run into an error such as 1118 or 1074:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs. ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT insteadMaximum TEXT lengthThe maximum size of a TEXT data type depends on which type of TEXT data type is being used. Because they are stored as objects, the only row overhead in the table object is a pointer (8 or 16 bytes). Here is a list of the maximum TEXT length, and the overhead (in the TEXT object):
TINYTEXT – up to 255 bytes, 1 byte overhead TEXT – up to 64 Kb, 2 bytes overhead MEDIUMTEXT – up to 16 Mb, 3 bytes overhead LONGTEXT – up to 4 Gb, 4 bytes overheadDEFAULT valuesMySQL does not allow TEXT data types to have a default value other than NULL. VARCHAR fields are allowed to be created with a DEFAULT value.
ConclusionsBecause of the storage implications, it is preferable to use VARCHAR instead of TINYTEXT.
If you need to have a DEFAULT value that is not NULL, you must use VARCHAR (or CHAR).
If you need to store strings longer than approximately 64 Kb, use MEDIUMTEXT or LONGTEXT. VARCHAR cannot support storing values that large.
Make sure you are aware of the effects of a multi-byte character set. VARCHAR(255) stores 255 characters, which may be more than 255 bytes.
乍一看，TEXT 和 VARCHAR 似乎可以存储相同的信息。但是，TEXT 字段和 VARCHAR 字段的工作方式之间存在根本差异，需要考虑这些差异。
标准VARCHAR 实际上是 ISO SQL:2003 标准的一部分；TEXT 数据类型（包括 TINYTEXT）是非标准的。
StorageTEXT 数据类型作为独立的对象与包含它们的表和结果集存储。这种存储是透明的——涉及 TEXT 字段的查询与涉及 VARCHAR 字段的查询的编写方式没有区别。由于 TEXT 不作为行的一部分存储，因此 TEXT 字段的检索需要额外的 [edited 1/22] 内存开销。
最大 VARCHAR 长度VARCHAR 的最大行长度受表的最大行长度限制。对于大多数存储引擎，这是 65,535 字节（NDB 具有不同的最大行值）。理论上，VARCHAR 的最大长度为 65,536 字节。开销进一步限制了 VARCHAR 的实际最大大小。
如果 VARCHAR 字段的最大长度为 0-255 字节，则存储 VARCHAR 字段的长度需要 1 个字节；如果大于 255 字节，则存储长度的开销为 2 字节。如果 VARCHAR 字段允许 NULL 值，则会增加额外的开销 — 对于每组 8 个允许 NULL 值的字段，每个表使用 1 个字节的开销。如果 VARCHAR 是表中唯一的行，并且不允许 NULL 值，则 VARCHAR 允许的最大长度为 65,532 字节。
请记住，VARCHAR(x) 中的数字表示字符数，而不是字节数。因此，如果字符集使用多字节字符（例如 UTF-8），则尝试定义仅包含 VARCHAR(65532) 的表时可能会遇到困难。
如果您尝试定义一个超过允许长度的 VARCHAR 值，您将遇到诸如 1118 或 1074 之类的错误：
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs. ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT instead最大 TEXT 长度TEXT 数据类型的最大大小取决于所使用的 TEXT 数据类型。因为它们是作为对象存储的，所以表对象中唯一的行开销是一个指针（8 或 16 字节）。这是最大 TEXT 长度和开销（在 TEXT 对象中）的列表：
TINYTEXT – up to 255 bytes, 1 byte overhead TEXT – up to 64 Kb, 2 bytes overhead MEDIUMTEXT – up to 16 Mb, 3 bytes overhead LONGTEXT – up to 4 Gb, 4 bytes overheadDEFAULT 值MySQL 不允许 TEXT 数据类型具有除 NULL 以外的默认值。允许使用 DEFAULT 值创建 VARCHAR 字段。
结论由于存储影响，最好使用 VARCHAR 而不是 TINYTEXT。
如果您需要一个非 NULL 的 DEFAULT 值，则必须使用 VARCHAR（或 CHAR）。
如果您需要存储长度超过大约 64 Kb 的字符串，请使用 MEDIUMTEXT 或 LONGTEXT。VARCHAR 不能支持存储这么大的值。
确保您了解多字节字符集的影响。VARCHAR(255) 存储 255 个字符，可能超过 255 个字节。
回答by Johan
In this case varcharis better.
在这种情况下varchar更好。
Note that varcharcan be from 1 to 65535 chars.
请注意，varchar可以是 1 到 65535 个字符。
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section E.7.4, “Table Column-Count and Row-Size Limits”.
VARCHAR 列中的值是可变长度的字符串。在 MySQL 5.0.3 之前，长度可以指定为 0 到 255 之间的值，在 5.0.3 及更高版本中可以指定为 0 到 65,535。MySQL 5.0.3 及更高版本中 VARCHAR 的有效最大长度受最大行大小（65,535 字节，在所有列之间共享）和使用的字符集的约束。请参阅第 E.7.4 节，“表列数和行大小限制”。
Blobs are saved in a separate section of the file.
They require an extra fileread to include in the data.
For this reason varchar is fetched much faster.
Blob 保存在文件的单独部分中。
它们需要额外的文件读取以包含在数据中。
出于这个原因，varchar 的获取速度要快得多。
If you have a large blob that you access infrequently, than a blob makes more sense.
Storing the blob data in a separate (part of the) file allows your core data file to be smaller and thus be fetched quicker.
如果您有一个不经常访问的大 blob，那么 blob 会更有意义。
将 blob 数据存储在单独的（部分）文件中可以让您的核心数据文件更小，从而更快地获取。
