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

根据定义:

VARCHAR: Llength 的范围是1到255个字符。VARCHAR 值以不区分大小写的方式进行排序和比较，除非给定 BINARY 关键字。X + 1字节
TINYBLOB，TINYTEXT: 最大长度为255(2 ^ 8-1)个字符 x + 1字节的 BLOB 或 TEXT 列

在此基础上，我创建了以下表格:

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: Llength 的范围大于255个字符。VARCHAR 值以不区分大小写的方式进行排序和比较，除非给定 BINARY 关键字。X + 2字节
最大长度为65535(2 ^ 16-1)个字符 x + 2字节的 BLOB 或 TEXT 列

74975 次浏览
小开

In this case varchar is better.

Note that varchar can be from 1 to 65535 chars.

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”.

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.

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.


提交答案