11.5. Data Type Storage Requirements
The storage requirements for each of the data types supported by
MySQL are listed here by category.
The maximum size of a row in a MyISAM table is
65,534 bytes. Each BLOB and
TEXT column accounts for only five to nine
bytes toward this size.
Storage Requirements for Numeric
Types
Values for DECIMAL (and
NUMERIC) columns are represented using a binary
format that packs nine decimal (base 10) digits into four bytes.
Storage for the integer and fractional parts of each value are
determined separately. Each multiple of nine digits requires four
bytes, and the “leftover” digits require some
fraction of four bytes. The storage required for excess digits is
given by the following table:
Storage Requirements for Date and Time
Types
Storage Requirements for String
Types
For the CHAR, VARCHAR, and
TEXT types, the values
L and M in
the preceding table should be interpreted as number of characters,
and lengths for these types in column specifications indicate the
number of characters. For example, to store a
TINYTEXT value requires
L characters plus one byte.
VARCHAR, VARBINARY, and the
BLOB and TEXT types are
variable-length types. For each, the storage requirements depend
on these factors:
The actual length of the column value
The column's maximum possible length
The character set used for the column
For example, a VARCHAR(10) column can hold a
string with a maximum length of 10. Assuming that the column uses
the latin1 character set (one byte per
character), the actual storage required is the length of the
string (L), plus one byte to record the
length of the string. For the string 'abcd',
L is 4 and the storage requirement is
five bytes. If the same column was instead declared as
VARCHAR(500), the string
'abcd' requires 4 + 2 = 6 bytes. Two bytes
rather than one are required for the prefix because the length of
the column is greater than 255 characters.
To calculate the number of bytes used to
store a particular CHAR,
VARCHAR, or TEXT column
value, you must take into account the character set used for that
column. In particular, when using the utf8
Unicode character set, you must keep in mind that not all
utf8 characters use the same number of bytes.
For a breakdown of the storage used for different categories of
utf8 characters, see
Section 10.7, “Unicode Support”.
Note: The
effective maximum length for a
VARCHAR or VARBINARY column
is 65,532.
The NDBCLUSTER storage engine in MySQL 5.1
supports true variable-width columns. This means that a
VARCHAR column in a MySQL Cluster table
requires the same amount of storage as it would using any other
storage engine. This represents a change in behavior from earlier
versions of NDBCLUSTER.
The BLOB and TEXT types
require 1, 2, 3, or 4 bytes to record the length of the column
value, depending on the maximum possible length of the type. See
Section 11.4.3, “The BLOB and TEXT Types”.
TEXT and BLOB columns are
implemented differently in the NDB Cluster storage engine, wherein
each row in a TEXT column is made up of two
separate parts. One of these is of fixed size (256 bytes), and is
actually stored in the original table. The other consists of any
data in excess of 256 bytes, which stored in a hidden table. The
rows in this second table are always 2,000 bytes long. This means
that the size of a TEXT column is 256 if
size <= 256 (where
size represents the size of the row);
otherwise, the size is 256 + size +
(2000 – (size – 256) %
2000).
The size of an ENUM object is determined by the
number of different enumeration values. One byte is used for
enumerations with up to 255 possible values. Two bytes are used
for enumerations having between 256 and 65,535 possible values.
See Section 11.4.4, “The ENUM Type”.
The size of a SET object is determined by the
number of different set members. If the set size is
N, the object occupies
(N+7)/8 bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A SET can
have a maximum of 64 members. See Section 11.4.5, “The SET Type”.
