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