Avoid future frustration in database design by understanding MySQL data types

by Contributor Melonfire | Dec 02, 2005 3:44:00 PM

Tags: Databases, Storage, Contributor Melonfire, integer value, data type, precision, database, RDBMS, MySQL

Takeaway: This document outlines the most important data types supported by MySQL and describes when and how each should be used.

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.

2 comment(s)