Quick MySQL Datatypes Comparison

Without looking, do you know the difference between MySQL’s text and mediumtext datatypes? How about the difference between int, mediumint, smallint, and tinyint? Unless you’re a Einstein level genius, you’re probably not going to be able to remember the differences between all datatypes, I know I can’t. So, I thought I’d post a comparison of MySQL datatypes. In the future I will probably come back and revise this blog post to show a more complete set of datatypes, but I
‘ll start off with the basics.

String Datatypes

Datatype Description
LONGTEXT Same as TEXT, but w/ max size of 4GB.
MEDIUMTEXT Same as TEXT, but w/ max size of 16MB.
TEXT Variable-length text w/ max size of 64K.
TINYTEXT Same as TEXT, but w/ max size of 255 bytes.
VARCHAR Same as CHAR, but stores just the text. The size is a maximum, not a minimum.
CHAR Fixed length string from 1 to 255 chars long. MySQL assumes CHAR(1) if not specified.
ENUM Accepts one of a predefined set of up to 64K strings.
SET Accepts zero or more of a predefined set of up to 64 strings.

Numeric Datatypes

Datatype Description
BIGINT Integer supporting numbers from -9223372036854775808 to 92233720368554775807 (or 0 to 18446744073709551615 if UNSIGNED)
INT (or INTEGER) Integer supporting numbers from -2147483648 to 2147483647 (or 0 to 4294967295 if UNSIGNED)
MEDIUMINT Integer supporting numbers from -8388608 to 8388607 (or 0 to 16777215 if UNSIGNED)
SMALLINT Integer supporting numbers from -32768 to 32767 (or 0 to 65535 if UNSIGNED)
TINYINT Integer supporting numbers from -128 to 128 (or 0 to 255 if UNSIGNED)
DECIMAL (or DEC) Floating point values with varying levels of precision.
DOUBLE Double precision floating point values.
FLOAT Single precision floating point values.
REAL 4 byte floating point values.
BOOLEAN (or BOOL) Boolean flag, either 0 or 1.
BIT A bit-field, from 1 to 64 bits wide. Before MySQL5 was basically the same as TINYINT.

Date and Time Datatypes

Datatype Description
DATE Date from 1000-01-01 to 9999-12-31 in the format YYYY-MM-DD.
TIME Time in the format HH:MM:SS.
DATETIME A combination of DATE and TIME.
TIMESTAMP Same as DATETIME (with a smaller range).
YEAR 2 or 4 digit year. 2 digit years range from 70 (1970) to 69 (2069). 4 digit years range from 1901 to 2155.

Binary Datatypes

Datatype Description
LONGBLOB Blob w/ max length of 4GB.
MEDIUMBLOB Blob w/ max length of 16MB.
BLOB Blob w/ max length of 64K.
TINYBLOB Blob w/ max length of 255 bytes.