Show Menu
Cheatography

MySQL 5.7 Data Types Cheat Sheet by

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

Numeric Types

 
Signed
Unsigned
Storage
TINYINT
127
255
1 byte
SMALLINT
32K
65K
2 bytes
MEDIUMINT
8M
16M
3 bytes
INT
231
232
4 bytes
BIGINT
263-1
264-1
8 bytes
FLOAT
   
4 bytes
DOUBLE
   
8 bytes
224 = 16M
231 = 2B
232 = 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 charac­ters. In partic­ular, 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, respec­tively.

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

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
< 28
TINYTEXT
L
+ 1 bytes, where
L
< 28 (256B)
TEXT
L
+ 2 bytes, where
L
< 216 (65KB)
BLOB
L
+ 2 bytes, where
L
< 216
MEDIUMTEXT
L
+ 3 bytes, where
L
< 224 (16MB)
MEDIUMBLOB
L
+ 3 bytes, where
L
< 224
LONGTEXT
L
+ 4 bytes, where
L
< 232 (4GB)
LONGBLOB
L
+ 4 bytes, where
L
< 232
JSON
L
+ 4 bytes, where
L
< 232
ENUM
1 or 2 bytes (65K max values)
SET
1, 2, 3, 4, or 8 bytes (64 max values)
28 = 256
216 = 65K
224 = 16M
232 = 4B
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          SQL Server Cheat Sheet
            MySQL Cheat Sheet by CodeConquest