sql - varchar(255) v tinyblob v tinytext

Question

我的附带问题是 tinyblob 和 tinytext 之间真的有什么区别吗？

相信我真正的问题是什么原因，如果有的话，我会选择 varchar(255) 而不是 tinyblob 或 tinytext？

Answer 1

主要是 storage requirements和内存处理/速度:

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

VARCHAR(M), VARBINARY(M):
L + 1 bytes if column values require 0 – 255 bytes,
L + 2 bytes if values may require more than 255 bytes

TINYBLOB, TINYTEXT:
L + 1 bytes, where L < 2⁸

此外，请参阅 this帖子:

For each table in use, MySQL allocates memory for 4 rows. For each of these rows CHAR(X)/VARCHAR(X) column takes up the X characters.

A TEXT/BLOB on the other hand is represented by a 8 byte pointer + a 1-4 byte length (depending on the BLOB/TEXT type). The BLOB/TEXT is allocated dynamicly on use. This will use less memory, but in some cases it may fragment your memory in the long run.

编辑:顺便说一句，blob 存储二进制数据而文本存储 ASCII，这是 TINYBLOB 和 TINYTEXT 之间的唯一区别。