Learning how to use the MySQL database properly can take quite a bit of time. It is not uncommon to select a less than optimal data type when setting up a new table. Multiple data types are available for each value that you wish to store. This MySQL data type tutorial is intended to help take some of the mystery out of the proper type selections for database columns.
The following is a summary of some of the more commonly used MySQL data types and explanations regarding when each should be used.
x is an integer value representing the maximum display width or number of digits displayed.
d represents the number of decimal places.
| BIGINT(x) | For storing very large integers with a numeric range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. When using the UNSIGNED attribute the range is 0 to 18,446,744,073,709,551,615. Yeah, this is for the really big integers. |
| BLOB | BLOB can store a maximum length of 65,535 bytes of data. Each value is stored using a two-byte length prefix that indicates the number of bytes in the value. |
| CHAR(x) | This is a fixed-length string that is always right-padded with spaces to the specified length (p) when stored. x represents the maximum number of characters to be stored. CHAR can store up to 255 characters. If you do not declare a value for p, the default value is 1. Trailing spaces are removed when you retrieve a CHAR data type. |
| DATE | The is the standard data type to use for storing dates. The date format is YYYY-MM-DD. supported range for dates is 1000-01-01 to 9999-12-31. |
| DATETIME | DATETIME is similar to DATE, but adds time elements. The standard format is: YYYY-MM-DD HH:MM:SS. Similar to DATE, the range of supported values is 1000-01-01 00:00:00 to 9999-12-31 23:59:59. |
| DECIMAL(x,d) | An unpacked fixed-point number stored as string. x is the total number of digits and d is the number of decimal places. |
| DOUBLE(x,d) | A double precision floating number with a range of -3-1.7976931348623157E+308 to -1.7976931348623157E+308. A double precision number can be a integer, fixed point or floating point value. It uses 8 bytes of storage. This data type is accurate to about 50 digits and 15 decimal places, so it can store large digit numbers and fractional numbers with a high degree of accuracy. Need to store a number like 245.14143548335? Use the double precision data type. x is the total number of digits and d is the number of decimal places. |
| ENUM(‘Y’,’N’) | ENUM is an enumeration, which is a string object that is restricted to only one value from a list. You can not store any value other than one of the values designated. This is good for setting flags used with check boxes, radio buttons or when a selection is made from a short list of values. You are not limited to ‘Y’ and ‘N’. Those are just examples. Any values can be used. If an ENUM column is declared NOT NULL, its default value is the first element in the list. |
| FLOAT(x,d) | A small single-precision floating-point decimal number with a range of -3.402823466E+38 to 3.402823466E+38. A single precision number can be a integer, fixed point or floating point value. It uses 4 bytes of storage. This data type is limited to up to 24 bits and 7 decimal places of precision. Single-precision does a good job with values such as 862.21 and 4999.92, but a number like 3.21543558 cannot be represented exactly because it contains too many decimal places. x is the total number of digits and d is the number of decimal places. |
| INT(x) INTEGER(x) |
A standard integer with a numeric range of -2,147,483,648 to 2,147,483,647. With the UNSIGNED attribute the range is 0 to 4,294,967,295. |
| LONGBLOB | LONGBLOB can store a maximum length of 4,294,967,295 bytes of data. Each value is stored using a 4-byte length prefix that indicates the number of bytes in the value. |
| LONGTEXT | A string data type with a maximum length of 4,294,967,295 characters. Use LONGTEXT if you need to store large blocks of text, such as a chapter of a book. |
| MEDIUMBLOB | MEDIUMBLOB can store a maximum length of 16,777,215 bytes of data. Each value is stored using a 3-byte length prefix that indicates the number of bytes in the value. |
| MEDIUMINT(x) | A medium-sized integer with a normal range of -8388608 to 8,388,607. When using the UNSIGNED attribute the range is 0 to 16,777,215. |
| MEDIUMTEXT | A string column type that can store a maximum of 16,777,215 characters. |
| SMALLINT(x) | A small integer with a numeric range of -32768 to 32767. With the UNSIGNED attribute the range is 0 to 65,535. |
| TEXT | A string data type that can store up to 65,535 characters. TEXT is commonly used for storing blocks of text used for brief articles. |
| TIME | This is simply a representation of a specific time using the format: HH:MM:SS. |
| TIMESTAMP | The range for a TIMESTAMP is 1970-01-01 00:00:01 UTC (Unix epoch + 1 second) to partway through the year 2038. It uses the format YYYY-MM-DD HH:MM:SS. A TIMESTAMP is most commonly used for recording updates or inserts to a MySQL table. If you do not assign a value to TIMESTAMP, the first entry is automatically set to the date and time of the most recent operation. |
| TINYBLOB | A small BLOB column with a maximum length of 255 bytes. |
| TINYINT(x) | This a very small integer with a range of -128 to +127. When the UNSIGNED attribute is used, the range starts from 0 and thus is 0 to 255. Used for storing small integer numbers, such as numeric flags like a status of 0 or 1. |
| TINYTEXT | A string data type that can store up to to 255 characters. |
| VARCHAR(x) | This is a variable length string. Unlike CHAR, it is not padded with spaces. p represents the maximum number of characters to be stored. Prior to MySQL 5.0.3, VARCHAR can store up to 255 characters. You can store 65,535 characters in MySQL 5.0.3 and newer versions. For most database applications, this is the most common and efficient method for storing short strings. |
| YEAR | A 2-digit or 4-digit representation of a year. A 4-digit year can range from 1901 to 2155. |
Several of the data types used by MySQL are legacy types, which means that they are based upon data types used during the evolution of relational databases. That is why there appears to be a lot of overlap among different data types.
What is a BLOB?
This is one of the first questions that comes up with anyone that is new to relational databases. A BLOB is a binary large object that can hold a variable amount of data. It is not restricted to text characters. You can store images and other objects using BLOBs. If you just want to store text, then use one of the TEXT data types. But if you want to store objects and data other than text, use a BLOB type. The maximum size for a BLOB is determined by its type, BLOB, MEDIUMBLOB, TINYBLOB or LONGBLOB.
Which data type do I use with currencies?
This is a matter of varying opinions. I think many people use DECIMAL(7,2) for storing $US and $CN currencies, but others recommend using INTEGER. Wiith INT or INTEGER, you would have to multiply a $US currency value by 100 prior to storing it (74.99 * 100 = 7499), and then divide by 100 and assign it to a variable when retrieving the value. I’ve also seen people use VARCHAR(7).
Popular shopping carts, such as ZenCart and osCommerce use DECIMAL(15,4), which covers international currencies. Obviously, there are several data types and variations that can be used.
For more detailed information about MySQL data types, check out the data types section of the official MySQL manual. We are also about the release a MySQL cheatsheet, which covers data types and other important aspects of the MySQL database.
TomasW says
Thank you for a great article. I’m working on a project where accurate precision is needed.
If a double in general is accurate down to 15 decimal places, is a field defined as double(14,5) or double(20,15) accurate down to 5 resp. 15 decimals? Do I need to maximize the D part of the double(M,D) definition in order to get accurate numbers?
JeffS says
Thank you so much, I wish this was the first article I found when searching for an overview of the data types… because it was by far the best I’ve seen! Thanks Again!
vynum says
varchar (x)
“For most database applications, this is the most common and efficient method for storing short strings.”
varchar (x) vs char (x)
This is not necessarily all true as it may seem to an average everyday reader. “efficient method” in the above sentence uses two words of great meaning but unclarity. “efficient method” is unclear as how the varchar command is efficient. From a MySql expert and MySql database perspective, what this means in the sentence above is limiting disk space only.
The VARCHAR’s only advantage over the CHAR command is limiting disk space. VARCHAR DOES NOT HAVE THE SPEED OF LOOKUPS AND RESULTSETS AS DOES CHAR DOES!!
TO CONSERVE DISK SPACE USE: varchar (x)
FOR SPEED OF RETREIVING RECORDS USE: char (x)
The only difference between the two is how the data is actually stored. You can still have “variable characters” in the CHAR! It’s just the reason that CHAR is so much more faster because it is padded with spaces. And under the MySqL Engine, this is a good thing. It makes insertion and retrieval much faster. Why? Its because the way MySql runs through all the rows and columns to find specific data. And, if the data is not padded in the field, such as when using the VARCHAR (x) MySql runs slower. Especially when your database grows and grows. Most of the time when DBA’s complain about why their database was fast in the beginning but slowed down after a while. This could very well be the culprit.
So, Disk-space is enormousness now days, and CPU speeds are lighting fast. So its up to you to make the call. But from judging that disk-space is cheap, and CPU’s are faster, I banking on CHAR over VARCHAR to be the winner.
But in today’s world of 2010, and how disk-space is very cheap, I would highly recommend using CHAR inplace of varchar. On a database of millions of records, the look-up and retrieval up records overwhelmingly faster.
Doogie says
Hi vynum
That’s good info. The statement doesn’t say anything about performance, but your points about performance are well-taken. The statement is accurate in that it is the most common form used and it does conserve disk disk space and therefore is more efficient from that perspective. Even though disk space is cheap these days, if you have a large table you may not want to burn up space unnecessarily with columns padded with spaces. It depends upon how variable the length of the data might be. However, if performance is a critical issue and every millisecond is important (it frequently is these days), the CHAR type looks like it has the benefits that you pointed out.
For anyone who wants to learn more about the differences, check out the MySQL page on this topic.
The CHAR and VARCHAR Types
Tejas says
I am working on a project in which I want to store Marathi(It,s a Indian language) on MySql DB
I am developing project in JSP
I am up to the stage where I am able to write Marathi words in browser text box
but unable to store the data in DB in proper format (it Stores garbage values)
can you suggest me to store & retrieve the data