Commonly used MySQL Datatypes

[Tony]

M - Indicates the maximum display size. The maximum legal display size is 255.

Numeric Types
INT[(M)]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
Example:
Age INT;

FLOAT[(M,D)]
Single-precision floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38.

Floating point numbers are made to be very precise. If specified as Price FLOAT(6,2) then 6 numbers would be allowed to the left of the decimal point, and 2 to the right.

Date and Time
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

Example:
Today DATE;

TIME
A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers.

Example:
Now TIME;

String Types
CHAR(M)
CHAR's are fixed length strings that are right-padded with space to whatever specified by the length in M. If specified a data type as Name CHAR(60), and the entry is only 20 characters in length, then the entry would be padded with 40 characters worth of spaces. These spaces will be removed when the value is retrieved though. These entries will be sorted and compared by MySQL in case-insensitive fashions unless you give the BINARY keyword.... The maximum size is 255 characters.

Example:
Name CHAR(60);

VARCHAR(M)
These strings are made of variable lengths (as the name kind of suggests). All of the trailing spaces will be removed when the value is stored in the database. This is great for when you have limited hard drive space, but it can be a problem performance-wise. This ca reduce speed by 50%. The explanation is that it actually loads the maximum length in anyway, and stores everything into memory. This causes the lag in performance. The maximum size is 255 characters.

Example:
Name VARCHAR(60);

BLOB or TEXT
BLOB or TEXT columns have a maximum length of 65535 (2^16 - 1) (BIG). They can hold text and are good at keeping large records, such as articles. The difference is that BLOB searches are case sensitive, while TEXT searches are case in-sensitive. These are used when your record size is between 255 and 65535 characters.

More information can be found at http://www.mysql.com/Manual_chapter/man ... rence.html