Source code editor What Is Ajax
Source code editor What Is Ajax
↑
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. This limitation may be shared by other storage engines as well.
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 would take 15 bytes in a table using a storage engine other than NDB 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 due to the alignment factor.
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.
You may find the ndb_size.pl utility to be useful for estimating NDB storage requirements. 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. See Section 15.8.14, “ndb_size.pl — NDBCluster Size Requirement Estimator”, for more information.
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( | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
FLOAT | 4 bytes |
DOUBLE [PRECISION], REAL | 8 bytes |
DECIMAL(, NUMERIC( | Varies; see following discussion |
BIT( | 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 |
Before MySQL 5.0.3, DECIMAL columns are represented as strings and storage requirements are: M+2 bytes if D > 0, M+1D = 0, D+2 if M < D
Storage Requirements for Date and Time Types
| Data Type | Storage Required |
DATE | 3 bytes |
TIME | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
YEAR | 1 byte |
The storage requirements shown in the table arise from the way that MySQL represents temporal values:
DATE: A three-byte integer packed as DD + MMЧ32 + YYYYЧ16Ч32
TIME: A three-byte integer packed as DDЧ24Ч3600 + HHЧ3600 + MMЧ60 + SS
DATETIME: Eight bytes:
A four-byte integer packed as YYYYЧ10000 + MMЧ100 + DD
A four-byte integer packed as HHЧ10000 + MMЧ100 + SS
TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)
YEAR: A one-byte integer
Storage Requirements for String Types
| Data Type | Storage Required |
CHAR( | <= 255 |
VARCHAR( | L characters + 1 byte, where <= 255 or L characters + 2 bytes, where <= 65535. Prior to MySQL 5.0.3: L characters + 1 byte, where <= 255. |
BINARY( | <= 255 |
VARBINARY( | L + 1 bytes, where <= 255 or L + 2 bytes, where <= 65535. Prior to MySQL 5.0.3: L + 1 bytes, where <= 255. |
TINYBLOB | L + 1 bytes, where L < 28 |
TINYTEXT | L characters + 1 byte, where L < 28 |
BLOB | L + 2 bytes, where L < 216 |
TEXT | L characters + 2 bytes, where L < 216 |
MEDIUMBLOB | L + 3 bytes, where L < 224 |
MEDIUMTEXT | L characters + 3 bytes, where L < 224 |
LONGBLOB | L + 4 bytes, where L < 232 |
LONGTEXT | L characters + 4 bytes, where L < 232 |
ENUM(' | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET(' | 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 to store the value plus one byte to store the length of the value.
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 and whether the value contains multi-byte characters. 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 and can require up to three bytes per character. For a breakdown of the storage used for different categories of utf8 characters, see Section 10.7, “Unicode Support”.
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, because some character sets contain multi-byte characters
For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. 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 to store the column length because the maximum length is greater than 255 characters.
Note: In MySQL 5.0.3 and later, the effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is 65,532. For a VARCHAR column that stores multi-byte characters, the maximum effective number of characters is less. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.
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.
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, 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 is 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 ( bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A N+7)/8SET can have a maximum of 64 members. See Section 11.4.5, “The SET Type”.