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.
