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. |