type
status
date
slug
summary
tags
category
icon
password
AI summary
Last edited time
Mar 7, 2024 04:34 AM
Numeric Type Storage Requirements
Data Type | Storage Required |
1 byte | |
2 bytes | |
3 bytes | |
4 bytes | |
8 bytes | |
FLOAT(p) | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
4 bytes | |
DOUBLE [PRECISION], REAL | 8 bytes |
DECIMAL(M,D), NUMERIC(M,D) | Varies; see following discussion |
BIT(M) | approximately ( M+7)/8 bytes |
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.Leftover Digits | Number of Bytes |
0 | 0 |
1 | 1 |
2 | 1 |
3 | 2 |
4 | 2 |
5 | 3 |
6 | 3 |
7 | 4 |
8 | 4 |
Date and Time Type Storage Requirements
For
TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.Data Type | Storage Required Before MySQL 5.6.4 | Storage Required as of MySQL 5.6.4 |
1 byte | 1 byte | |
3 bytes | 3 bytes | |
3 bytes | 3 bytes + fractional seconds storage | |
8 bytes | 5 bytes + fractional seconds storage | |
4 bytes | 4 bytes + fractional seconds storage |
As of MySQL 5.6.4, storage for
YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.Fractional Seconds Precision | Storage Required |
0 | 0 bytes |
1, 2 | 1 byte |
3, 4 | 2 bytes |
5, 6 | 3 bytes |
For example,
TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage.String Type Storage Requirements
In the following table,
M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.Data Type | Storage Required |
CHAR(M) | The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set. |
BINARY(M) | M bytes, 0 <= M <= 255 |
VARCHAR(M), VARBINARY(M) | L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes |
L + 1 bytes, where L < 28 | |
L + 2 bytes, where L < 216 | |
L + 3 bytes, where L < 224 | |
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) |
Spatial Type Storage Requirements
MySQL stores geometry values using 4 bytes to indicate the SRID followed by the WKB representation of the value. The
LENGTH() function returns the space in bytes required for value storage.JSON Storage Requirements
In general, the storage requirement for a
JSON column is approximately the same as for a LONGBLOB or LONGTEXT column; that is, the space consumed by a JSON document is roughly the same as it would be for the document's string representation stored in a column of one of these types. However, there is an overhead imposed by the binary encoding, including metadata and dictionaries needed for lookup, of the individual values stored in the JSON document. For example, a string stored in a JSON document requires 4 to 10 bytes additional storage, depending on the length of the string and the size of the object or array in which it is stored.In addition, MySQL imposes a limit on the size of any JSON document stored in a
JSON column such that it cannot be any larger than the value of max_allowed_packet.