MySQL storage types and their limits

Posted on 02.10.17   MySQL Storage

Below are some of the most used data types and its storage requirements:

Data type Storage required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
 BIGINT 8 bytes
FLOAT 4 bytes
DOUBLE, REAL 8 bytes
VARCHAR(x) x+1 byte if value is less than 255 bytes and x+2 bytes if over 255 bytes.
TINYTEXT 255 bytes
TEXT 65 535 bytes
MEDIUMTEXT 16 777 215 bytes
LONGTEXT 4 294 967 295 bytes

Notes:

  • Characters can take up different amount of space depending on character encoding. Regular English ASCII characters only take 1 byte per character, meaning that a TEXT column can fit 65 535 characters (a-Z, 0-9 and some special signs)
  • VARCHAR(180) will require 181 bytes of space, regardless if it’s empty or not.

Date formats:

Data type < MySQL 5.6.4 > MySQL 5.6.4
YEAR 1 byte 1 byte
DATE 3 bytes 3 bytes
TIME 3 bytes 3 bytes + fractional seconds storage
DATETIME 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP 4 bytes 4 bytes + fractional seconds storage

Sources:

More on ITDB

How to debug a non starting application with ProcMon


Finding the source for locked AD account from Event Viewer log on Domain controller


Create a cmd alias/shortcut for issuing commands on remote computer


Change Resize Mode for all VMs in RoyalTS 4


Telnet alternative to check if port is open on host