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.|
|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.