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.
Important: For tables using the
NDBCluster storage engine, there is the factor
of 4-byte alignment to be taken into
account when calculating storage requirements. This means that all
NDB data storage is done in multiples of 4
bytes. Thus, a column value that — in a table using a
storage engine other than NDB — would
take 15 bytes for storage, requires 16 bytes in an
NDB table. This requirement applies in addition
to any other considerations that are discussed in this section.
For example, in NDBCluster tables, the
TINYINT, SMALLINT,
MEDIUMINT, and INTEGER
(INT) column types each require 4 bytes storage
per record.
In addition, when calculating storage requirements for Cluster
tables, you must remember that every table using the
NDBCluster storage engine requires a primary
key; if no primary key is defined by the user, then a
“hidden” primary key will be created by
NDB. This hidden primary key consumes 31-35
bytes per table record.
When calculating Cluster memory requirements, you may find useful
the ndb_size.pl utility which is available on
MySQLForge. This
Perl script connects to a current MySQL (non-Cluster) database and
creates a report on how much space that database would require if
it used the NDBCluster storage engine.
Storage Requirements for Numeric Types
| Data Type | Storage Required |
TINYINT
|
1 byte |
SMALLINT
|
2 bytes |
MEDIUMINT
|
3 bytes |
INT, INTEGER |
4 bytes |
BIGINT
|
8 bytes |
FLOAT(p) |
4 bytes if 0 <= p <= 24, 8 bytes if 25
<= p <= 53 |
FLOAT
|
4 bytes |
DOUBLE [PRECISION], REAL |
8 bytes |
DECIMAL(M,D),
NUMERIC(M,D) |
Varies; see following discussion |
BIT(M) |
approximately (M+7)/8 bytes |
The storage requirements for DECIMAL (and
NUMERIC) are version-specific:
As of MySQL 5.0.3, values for DECIMAL 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:
| Leftover Digits | Number of Bytes |
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 4 |
| 8 | 4 |
| 9 | 4 |
Before MySQL 5.0.3, DECIMAL columns are
represented as strings and storage requirements are:
M+2 bytes if
D > 0,
M+1 bytes if
D = 0 (D+2,
if M <
D)
Storage Requirements for Date and Time Types
| Data Type | Storage Required |
DATE
|
3 bytes |
DATETIME
|
8 bytes |
TIMESTAMP
|
4 bytes |
TIME
|
3 bytes |
YEAR
|
1 byte |
Storage Requirements for String Types
| Data Type | Storage Required |
CHAR(M) |
M bytes, 0 <=
M <= 255 |
VARCHAR(M) |
Prior to MySQL 5.0.3: L
+ 1 bytes, where L
<= M and 0
<= M
<= 255. MySQL 5.0.3 and
later: L + 1 bytes,
where L <=
M and 0
<= M
<= 255 or
L + 2 bytes, where
L <=
M and 256
<= M
<= 65535 (see note below). |
BINARY(M) |
M bytes, 0 <=
M <= 255 |
VARBINARY(M) |
Prior to MySQL 5.0.3: L
+ 1 bytes, where L
<= M and 0
<= M
<= 255. MySQL 5.0.3 and
later: L + 1 bytes,
where L <=
M and 0
<= M
<= 255 or
L + 2 bytes, where
L <=
M and 256
<= M
<= 65535 (see note below). |
TINYBLOB, TINYTEXT |
L+1 byte, where L
< 28 |
BLOB, TEXT |
L+2 bytes, where L
< 216 |
MEDIUMBLOB, MEDIUMTEXT |
L+3 bytes, where L
< 224 |
LONGBLOB, LONGTEXT |
L+4 bytes, where L
< 232 |
ENUM('value1','value2',...) |
1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET('value1','value2',...) |
1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
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: In MySQL 5.0.3 and later,
the effective maximum length for a
VARCHAR or VARBINARY column
is 65,532.
As of MySQL 5.0.3, the NDBCLUSTER engine
supports only fixed-width columns. This means that a
VARCHAR column from a table in a MySQL Cluster
will behave as follows:
-
If the size of the column is fewer than 256 characters, the column requires one byte extra storage per row.
-
If the size of the column is 256 characters or more, the column requires two bytes extra storage per row.
Note that the number of bytes required per character varies
according to the character set used. For example, if a
VARCHAR(100) column in a Cluster table uses the
utf8 character set, then each character
requires 3 bytes storage. This means that each record in such a
column takes up 100 × 3 + 1 = 301 bytes for
storage, regardless of the length of the string actually stored in
any given record. For a VARCHAR(1000) column in
a table using the NDBCLUSTER storage engine
with the utf8 character set, each record will
use 1000 × 3 + 2 = 3002 bytes storage; that is,
the column is 1,000 characters wide, each character requires 3
bytes storage, and each record has a 2-byte overhead because 1,000
> 256.
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”.