W3cubDocs

/MariaDB

Data Type Storage Requirements

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

Numeric Data Types

Data Type Storage Requirement
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
BIGINT 8 bytes
FLOAT(p) 4 bytes if p <= 24, otherwise 8 bytes
DOUBLE 8 bytes
DECIMAL See 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 digits Storage Requirement
0 0 bytes
1 1 byte
2 1 byte
3 2 bytes
4 2 bytes
5 3 bytes
6 3 bytes
7 4 bytes
8 4 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 Type Storage Requirement
ENUM 1 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, TINYTEXT len + 1 bytes
BLOB, TEXT len + 2 bytes
MEDIUMBLOB, MEDIUMTEXT len + 3 bytes
LONGBLOB, LONGTEXT len + 4 bytes
SET Given 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.

When using field COMPRESSED attribute, 1 byte is reserved to metadata, for example VARCHAR(255) will use +2bytes instead of +1

Examples

Assuming a single-byte character-set:

Value CHAR(2) Storage Required VARCHAR(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 Type Storage Requirement
DATE 3 bytes
TIME 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
YEAR 1 byte

Microseconds

MariaDB 5.3 and MySQL 5.6 introduced microseconds. The underlying storage implementations were different, but from MariaDB 10.1, MariaDB defaults to the MySQL format (by means of the mysql56_temporal_format variable). Microseconds have the following additional storage requirements:

MySQL 5.6+ and MariaDB 10.1+

Precision Storage Requirement
0 0 bytes
1,2 1 byte
3,4 2 bytes
5,6 3 bytes

MariaDB 5.3 - MariaDB 10.0

Precision Storage Requirement
0 0 bytes
1,2 1 byte
3,4,5 2 bytes
6 3 bytes
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/data-type-storage-requirements/