MySQL Data Types

I couldn’t find a nice table with all the data types and the limits associated with them in it, so I thought I’d trawl the web for each individual one and make one. This table is by no means exhaustive, but it does contain most of the common ones and will be updated as I need to use more of them for WebLex it also assumes that mysql 4.1 or greater is being used. Else some default values might be incorrect.

Data Type What can it hold?
INT/INTEGER An integer in the range -2147483648 to 2147483647. Or, if set to unsigned the range is 0 to 4294967295.
TINYINT An integer in the range -128 to 127. Or, if set to unsigned the range is 0 to 255.
SMALLINT An integer in the range -32768 to 32767. Or, if set to unsigned the range is 0 to 65535.
MEDIUMINT An integer in the range -8388608 to 8388607. Or, if set to unsigned the range is 0 to 16777215.
BIGINT An integer in the range -9223372036854775808 to 9223372036854775807. Or, if set to unsigned the range is 0 to 18446744073709551615.
TEXT Contains text upto a maximum length of 65535 characters.
TINYEXT Contains text upto a maximum length of 255 characters.
MEDIUMTEXT Contains text upto a maximum length of 16777215 characters.
LONGTEXT Contains text upto a maximum length of 4294967295 characters.
ENUM(‘x’,’y’,’z’,…) Can only have a value as specified, by you, in the list of values in brackets. In this case it could only take ‘x’, ‘y’, or ‘z’. It can also take NULL or the special “” error value specified when the table is created. You can specify a maximum of 65535 values for an ENUM to take.
SET(‘x’,’y’,’z’,…) Very similar to ENUM except that more than one value can be assigned, so data could be both ‘x’ and ‘y’, or ‘y’ AND ‘z’. However, only 64 different values can be specified in a set.
CHAR(x) Holds x characters, where 0≤x≤255. The default value of x (i.e. if just “CHAR” is used) is 1. If a value is entered that is less than x characters long, it will be appended with spaces until it is x characters long. However, upon retrieval, these spaces will be removed automatically.
VARCHAR(x) Similar to char, except x is considered an “upto” value. Values are stored using only as many characters as they contain, plus an extra byte recording the length of the value. Any trailing spaces are removed from the value when it is stored in the database.