Data stored in a database comes in all shapes and sizes.
Some fields store only numbers, some only text, and others a combination of the
two. Some databases also support specialized types: dates and times, binary
strings and Booleans.

Selecting from available data types to find the best match
for your data is an important part of database design, because such type
selection affects the efficiency and performance of your RDBMS. Therefore, it’s
extremely important to be fully aware of the options available to you in your
RDBMS, and to select the most appropriate data type for your storage needs at
the time of design itself.

That’s where this document comes in. It outlines (Table A) the most important data types
supported by MySQL, one of the most popular free RDBMS currently
available, and describes when and how each should be used. It thus serves as a
ready resource to help you in creating an optimal design for your databases.

Table A

Data Type

Description

Bytes Used

Recommended Use
SMALLINT

Integer
values in the range -32000 to +32000 (appx)

2

Storing
relatively small integer values.

Examples:
Age, quantity
INT

Integer
values in the range -2000000000 to +2000000000 (appx)

4

Storing
medium integer values.

Example:
Distance
BIGINT

Extremely
large integer values that do not fit into either SMALLINT or INT fields

8

Storing
large integer values.

Example:
Scientific/mathematical values
FLOAT

Single-precision
floating-point values

4

Storing
decimal values

Examples:
Measurement, temperature

DOUBLE

Double-precision
floating-point values

8

Storing
decimal values which require double precision

Examples: Scientific values
DECIMAL

Floating-point
values with user-defined precision

Variable;
depends on precision and scale

Storing
decimal values which require very high precision

Examples: Currency amounts, scientific values
CHAR

Fixed-length
strings

Specified
string length (up to 255 char)

Storing
string values which will always contain a preset number of characters.

Examples:
Airline, country or post codes
VARCHAR

Variable-length
strings, with a preset maximum limit

Variable;
1 + actual string length (up to 255 char)

Storing
string values of varying length (up to a specified maximum limit).

Examples:
Names, passwords, short text labels
TEXT

Variable-length
strings with no maximum limit

Variable;
2 +  actual string length

Storing
large blocks of textual data

Examples:
News stories, product descriptions

 
BLOB

Binary
strings

Variable;
2 + actual string length

Storing
binary data

Examples: Images, attachments, binary documents

 
DATE

Date
values in the format yyyy-mm-dd

3

Storing
dates

Examples:
Birthdays, product expiry dates,

TIME

Time
values in the format hh:mm:ss

3

Storing
times or time intervals

Example:
Alarms, interval between two timestamps, task start/end times
DATETIME

Combined
date and time values in the format yyyy-mm-ddhh:mm:ss

8

Storing
combined date and time values

Examples: Reminders, events
TIMESTAMP

Combined
date and time values in the format yyyy-mm-ddhh:mm:ss

4

Recording
time instants

Examples: Event triggers, “last log-in”
timestamps
YEAR

Year
values in the format yyyy

1

Storing
year values

Examples: Graduation years, birth years
ENUM

A
set of values from which the user must select one.

1
or 2 bytes

Storing
string attributes which are mutually exclusive to each other

Examples: Boolean selections like Gender
SET

A
set of values from which the user can select zero, one or more.

Between
1 and 8 bytes; depends on set size

Storing
string attributes which can be selected in combination with each other

Examples: Multiple-choice selections like Hobbies and
Interests

For a complete list and detailed descriptions, see the MySQL manual. You should
also read the article entitled Choosing
the Right Type for a Column.