MySQL Datatypes

Each column in a table is made of a data type. There are three general types of columns then, numeric types, date and time types, and string (character) types. In the examples below, I will only show the most commonly used data types. More information can be found at

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;

This would be a valid integer if it is within the -2147483648 to 2147483647 values if it is unsigned, which it would be of course as no one is younger or older then those numbers... obviously.

FLOAT[(M,D)]

A small (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 I specified Price FLOAT(6,2) then 6 numbers would be allowed to the left of the decimal point, and 2 to the right. Very nice indeed.

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 you specify the length as in M. If you specify 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. You can lose up to 50% of speed because of this. 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, obviously of course, 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.