MySQL Variable Types | HostGator Support
  1. Knowledge Base
  2. >
  3. Results
  4. >
  5. MySQL Variable Types

MySQL Variable Types

In a MySQL database, there are three (3) main data types: text, numbers and dates/times.  When you design your database, it is important that you select the appropriate type, since this determines why type of data you can store in that column.  Using the most appropriate type can also increase the database's overall performance.

Text Types

CHAR( )A fixed section from 0 to 255 characters long.
VARCHAR( )A variable section from 0 to 255 characters long.
TINYTEXTA string with a maximum length of 255 characters.
TEXTA string with a maximum length of 65535 characters.
BLOBA string with a maximum length of 65535 characters.
MEDIUMTEXTA string with a maximum length of 16777215 characters.
MEDIUMBLOBA string with a maximum length of 16777215 characters.
LONGTEXTA string with a maximum length of 4294967295 characters.
LONGBLOBA string with a maximum length of 4294967295 characters.

The ( ) brackets allow you to specify the maximum  number of characters that can be used in the column.

BLOB stands for Binary Large OBject, and can be used to store non-text information that is encoded into text.


Number Types

TINYINT ( )-128 to 127 normal0 to 255 UNSIGNED
SMALLINT( )-32768 to 32767 normal0 to 65535 UNSIGNED
MEDIUMINT( )-8388608 to 8388607 normal0 to 16777215 UNSIGNED
INT( )-2147483648 to 2147483647 normal0 to 4294967295 UNSIGNED
BIGINT( )-9223372036854775808 to 9223372036854775807 normal0 to 18446744073709551615 UNSIGNED
FLOATA 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.

By default, the integer types will allow a range between a negative number and a positive number, as indicated in the table above.  You can use the UNSIGNED commend, which will instead only allow positive numbers, which start at 0 and count up.


Date/Time Types

DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MM:SS
TIMESTAMPYYYYMMDDHHMMSS
TIMEHH:MM:SS
YEARYYYY

Date/Time fields will only accept a valid date or time.