MySQL中的char/varchar/text/longtext列即使在部分填充的单元格中也会占用文件系统中的全部大小吗? [英] Do char / varchar / text / longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

问题描述

MySQL中的varchar/text/longtext列即使在单元格不完整的情况下，也会在文件系统中占据全长大小吗?

Do varchar/text/longtext columns in MySQL occupy full length size in filesystem even for incomplete cells?

请参考此博客文章

Referring to this blog article for example I have the following columns and i have put in a given string in it.

CHAR (72) - can store upto 72 characters - i put in a string "abcd"
VARCHAR (72) - can store upto 72 characters - i put in a string "abcd"
TINYTEXT - can store upto 255 characters - i put in a string "abcd"
TEXT - can store upto 65,535 characters - i put in a string "abcd"
LONGTEXT - can store upto 4,294,967,295 characters - i put in a string "abcd"

这些列中的每一列将占用多少实际物理空间?

How much actual physical space will be occupied by each of these columns for a row?

每列最多可以存储的完整大小?

The full size upto which each column is capable of storing?

还是仅存储"abcd"所需的数量?

Or only as much as required for storing "abcd"?

推荐答案

查看 http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

基本上，除CHAR以外的所有这些类型都是可变长度.

Basically, all of these types except for the CHAR are variable length.

例如，如果您有VARCHAR(72)并向其中写入abcd，则将存储5个字节.每个字符4个字节，并以1个字节的前缀存储字符串的长度(为4).

For instance, if you have VARCHAR(72) and you write abcd into it, you will store 5 bytes. 4 bytes for each character, and a 1 byte prefix to store the length of the string (which is 4).

如果字符串的长度超过255个字符，则VARCHAR前缀将为2个字节.因此，其中存储有256个字符串的VARCHAR(300)将占用258个字节.

If the length of the string is over 255 characters, then the VARCHAR prefix will be 2 bytes. So a VARCHAR(300) with a 256 character string stored in it will take up 258 bytes.

TINYTEXT始终具有1个字节的前缀，因为您只能在其中存储255个字符，因此abcd将占用5个字节.

TINYTEXT has a 1 byte prefix always, because you can only store 255 characters in it, so abcd would take 5 bytes.

TEXT的前缀为2个字节，因此abcd为6个字节.

TEXT has a 2 byte prefix, so abcd would be 6 bytes.

LONGTEXT的前缀为4个字节，因此abcd为8个字节.

LONGTEXT has a 4 byte prefix, so abcd would be 8 bytes.

最后，几乎没有用的CHAR类型.不管您存储在哪个目录中，CHAR(72)都将始终占用72个字节.实际上，它仅对超级短字段有用，该字段中的字符始终完全相同.像Y或N一样是很好的CHAR(1)候选者.

Lastly, there's the nearly useless CHAR type. A CHAR(72) will always take up 72 bytes no matter what you store in it. It's really only useful for super short fields where there is always the exact same number of characters in the field. Like Y or N would be a good CHAR(1) candidate.

这篇关于MySQL中的char/varchar/text/longtext列即使在部分填充的单元格中也会占用文件系统中的全部大小吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！