MySQL 5.7 Data Types Cheat Sheet

A Programming › MySQL cheat sheet in English by beeftornado

Data types, storage requirements, and max values updated for mysql 5.7

9,786 views 2,481 downloads 8 Aug 2018 History

PRs

3.7 avg · 0 ratings

Numeric Types

	Signed	Unsigned	Storage
TINYINT	127	255	1 byte
SMALLINT	32K	65K	2 bytes
MEDIUMINT	8M	16M	3 bytes
INT	2³¹	2³²	4 bytes
BIGINT	2⁶³-1	2⁶⁴-1	8 bytes
FLOAT			4 bytes
DOUBLE			8 bytes

2²⁴ = 16M
2³¹ = 2B
2³² = 4B

Date and Time Types

YEAR	1 byte
DATE	3 bytes
TIME	3 bytes + fractional seconds storage
DATETIME	5 bytes + fractional seconds storage
TIMESTAMP	4 bytes + fractional seconds storage

NOTE ON CHARSET

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 multibyte characters. In particular, when using a utf8 Unicode character set, you must keep in mind that not all characters use the same number of bytes. utf8mb3 and utf8mb4 character sets can require up to three and four bytes per character, respectively.

All columns share a maximum row size of 65,535 bytes, excluding BLOB and TEXT columns.

Official Documentation

String Types

CHAR(`M`)	`M` bytes, up to 255
BINARY(`M`)	`M` bytes, up to 255
VARCHAR(`M`)	`M` + 1 bytes, up to 255
	`M` + 2 bytes, more than 255 (65KB)
TINYBLOB	`L` + 1 bytes, where `L` < 2⁸
TINYTEXT	`L` + 1 bytes, where `L` < 2⁸ (256B)
TEXT	`L` + 2 bytes, where `L` < 2¹⁶ (65KB)
BLOB	`L` + 2 bytes, where `L` < 2¹⁶
MEDIUMTEXT	`L` + 3 bytes, where `L` < 2²⁴ (16MB)
MEDIUMBLOB	`L` + 3 bytes, where `L` < 2²⁴
LONGTEXT	`L` + 4 bytes, where `L` < 2³² (4GB)
LONGBLOB	`L` + 4 bytes, where `L` < 2³²
JSON	`L` + 4 bytes, where `L` < 2³²
ENUM	1 or 2 bytes (65K max values)
SET	1, 2, 3, 4, or 8 bytes (64 max values)

MySQL 5.7 Data Types Cheat Sheet

Numeric Types

Date and Time Types

NOTE ON CHARSET

String Types

Badges

Comments