The following tables indicate the approximate data storage requirements for each data type.

Numeric data types

Data TypeStorage Requirement
TINYINT1 byte
SMALLINT2 bytes
MEDIUMINT3 bytes
INT4 bytes
BIGINT8 bytes
FLOAT(p)4 bytes if p <= 24, otherwise 8 bytes
DOUBLE8 bytes
DECIMALSee table below
BIT(M)(M+7)/8 bytes

Note that MEDIUMINT columns will require 4 bytes in memory (for example, in InnoDB buffer pool).

Decimal

Decimals are stored using a binary format, with the integer and the fraction stored separately. Each nine-digit multiple requires 4 bytes, followed by a number of bytes for whatever remains, as follows:

Remaining digitsStorage Requirement
00 bytes
11 byte
21 byte
32 bytes
42 bytes
53 bytes
63 bytes
74 bytes
84 bytes

String data types

In the descriptions below, M is the declared column length (in characters or in bytes), while len is the actual length in bytes of the value.

Data TypeStorage Requirement
ENUM1 byte for up to 255 enum values, 2 bytes for 256 to 65,535 enum values
CHAR(M)M × w bytes, where w is the number of bytes required for the maximum-length character in the character set
BINARY(M)M bytes
VARCHAR(M), VARBINARY(M)len + 1 bytes if column is 0 – 255 bytes, len + 2 bytes if column may require more than 255 bytes
TINYBLOB, TINYTEXTlen + 1 bytes
BLOB, TEXTlen + 2 bytes
MEDIUMBLOB, MEDIUMTEXTlen + 3 bytes
LONGBLOB, LONGTEXTlen + 4 bytes
SETGiven M members of the set, (M+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes

In some character sets, not all characters use the same number of bytes. utf8 encodes characters with one to three bytes per character, while utf8mb4 requires one to four bytes per character.

Examples

Assuming a single-byte character-set:

ValueCHAR(2)Storage RequiredVARCHAR(2)Storage Required
''' '2 bytes''1 byte
'1''1 '2 bytes'1'2 bytes
'12''12'2 bytes'12'3 bytes

Date and time data types

Data TypeStorage Requirement
DATE3 bytes
TIME3 bytes
DATETIME8 bytes
TIMESTAMP4 bytes
YEAR1 byte

Comments

Comments loading...
Loading