mysql - varchar(255) vs tinytext/tinyblob 和 varchar(65535) vs blob/text

Question

根据定义:

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

所以基于此，我创建了下表:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `lastname` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

或者创建一个 varchar 或 tinytext 以及为什么更好？

是否也一样:

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

Answer 1

在这种情况下 varchar 更好。

请注意，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”.

Blob 保存在文件的单独部分中。
它们需要额外的文件读取才能包含在数据中。
因此，获取 varchar 的速度要快得多。

如果您有一个不常访问的大型 blob，那么 blob 更有意义。
将 blob 数据存储在单独的(部分)文件中可以让您的核心数据文件更小，从而更快地获取。