Hello.
Will a TEXT field use the same storage space in the database no matter if it is empty, got a few characters or filled to the limit?
+1
A:
The manual page Data Type Storage Requirements describes the storage requirements of each datatype for the MyISAM engine in more detail.
Data Type Storage Required
CHAR(M) M × w* bytes, 0 <= M <= 255
VARCHAR(M) L + 1 bytes if column values require 0 – 255 bytes
L + 2 bytes if values may require more than 255 bytes
TINYTEXT L + 1 bytes, where L < 2^8
TEXT L + 2 bytes, where L < 2^16
*w is the number of bytes required for the maximum-length character in the character set
So in conclusion the number of bytes of storage required to store a string in a TEXT column depends on the length of the string. This differs from CHAR(100) where the same amount of storage is required regardless of the length of the string stored.
Mark Byers
2010-08-29 20:57:50
I think the user means the `TEXT` type in MySQL, described on http://dev.mysql.com/doc/refman/5.1/en/blob.html.
MvanGeest
2010-08-29 21:00:18
@Mark Byers: I think the user actually meant that `TEXT` type, and not `CHAR` or `VARCHAR` (though the manual says `TEXT` can be regarded as `VARCHAR` in most respects).
MvanGeest
2010-08-29 21:03:01
Oh I see what you mean, he specifically wanted to know about the TEXT column type. I didn't notice that it was capitalized in the title (I was answering based on the body of the question). I'll updated my answer to make it more specific.
Mark Byers
2010-08-29 21:03:05