MySQL Data Types Reference

For all you developers and database designers out there, especially those of you using PHP and MySQL, I thought I would put up a handy quick reference to common MySQL data types. It’s nothing complex, I know, but I myself don’t try to keep it all committed to memory. One thing I learned a long time ago was to write stuff down! In this day and age that could also be typed into a text file or entered into a wiki, the point is that if you record whatever information you have to remember somewhere, you can always go back and pull it up. The advantage to this is that you don’t have to worry about remembering something incorrectly, and it frees up space in the old noggin’ for more important things, like memorizing pickup lines or quotes from SpongeBob Squarepants. So, after saying all that, here is my MySQL data types reference. I say it’s for MySQL but it should be useful to pretty much any database you are using I would think.

TEXT TYPES

CHAR( )	        A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	        A string with a maximum length of 65535 characters.
BLOB	        A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

NUMBER TYPES

TINYINT( )	-128 to 127 normal, 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal, 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal, 0 to 16777215 UNSIGNED.
INT( )	        -2147483648 to 2147483647 normal, 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal,
                0 to 18446744073709551615 UNSIGNED.
FLOAT	        A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed
                decimal point.

DATE TYPES

DATE	        YYYY-MM-DD.
DATETIME	YYYY-MM-DD HH:MM:SS.
TIMESTAMP	YYYYMMDDHHMMSS.
TIME	        HH:MM:SS.

MISC TYPES

ENUM ( )	Short for ENUMERATION which means that each column may have
                one of a specified possible values.
SET	        Similar to ENUM except each column may have more than one
                of the specified possible values.

There you have it, I hope you find it useful!

Leave a Reply

Your email address will not be published. Required fields are marked *