MySQL Data Type: An Overview of the Data Types in MySQL
Review the various data types available in MySQL to help better understand which ones you should use and when.
Join the DZone community and get the full member experience.
Join For FreeOne cannot handle the vast amount of data present in the world without a proper database management system. MySQL is one of the most popular database management systems used in the industry. In my previous blog on MySQL Tutorial, you would have got an understanding of the various SQL queries that can be executed. In this blog on MySQL Data Types, I will be discussing the different data types used in MySQL.
In this article on MySQL Data Types, I’m going to cover the following:
So, let’s get started with each one of them.
MySQL Data Types: Numeric Types
Numeric Datatypes allow both signed and unsigned integers. MySQL supports the following numeric data types.
| Data Type | Description | Storage |
| TINYINT(size) | Allows signed integers -128 to 127 and 0 to 255 unsigned integers. | 1 byte |
| SMALLINT(size) | Allows signed integers from -32768 to 32767 and 0 to 65535 unsigned integers. | 2 bytes |
| MEDIUMINT(size) | Allows signed integers from -8388608 to 8388607 and 0 to 16777215 unsigned integers. | 3 bytes |
| INT(size) | Allows signed integers from -2147483638 to 214747483637 and 0 to 4294967925 unsigned integers. | 4 bytes |
| BIGINT(size) | Allows signed integers from -9223372036854775808 to 9223372036854775807 and 0 to 18446744073709551615 unsigned integers. | 8 bytes |
| FLOAT(size,d) | Allows small numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. | 4 bytes |
| DOUBLE(size,d) | Allows large numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. | 8 bytes |
| DECIMAL(size,d) |
Allows storing DOUBLE as a string, so that there is a fixed decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal. | Varies |
MySQL Data Types: String Types
String Data types allow both fixed and variable length strings. MySQL supports the following String data types.
| Data Type | Description | Storage |
| CHAR(size) | Holds up to 255 characters and allows a fixed length string. | (Declared column length of characters * Number of bytes) <= 255 |
| VARCHAR(size) | Holds up to 255 characters and allows a variable length string. If you store characters greater than 55, then the data type will be converted to TEXT type. |
|
| TINYTEXT | Allows a string with a maximum length of 255 characters | Actual length in bytes of String value(Len) + 1 bytes, where Len < 28 |
| TEXT | Allows a string with a maximum length of 65,535 characters | Actual length in bytes of String value(Len) + 2 bytes, where Len < 216 |
| BLOB | Holds up to 65,535 bytes of data, and is used for Binary Large Objects. | Actual length in bytes of String value(Len) + 2 bytes, where Len < 216 |
| MEDIUMTEXT | Allows a string with a maximum length of 16,777,215 characters | Actual length in bytes of String value(Len) + 3 bytes, where Len < 224 |
| MEDIUMBLOB | Holds up to 16,777,215 bytes of data, and is used for Binary Large Objects. | Actual length in bytes of String value(Len) + 3 bytes, where Len < 224 |
| LONGTEXT | Allows a string with a maximum length of 4,294,967,295 characters | Actual length in bytes of String value(Len) + 4 bytes, where Len < 232 |
| LONGBLOB | Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects. | Actual length in bytes of String value(Len) + 4 bytes, where Len < 232 |
| ENUM(x,y,z,etc.) | Allows you to enter a list of possible values, with the maximum to be 65535 values. Just in case a value is inserted which is not present in the list, a blank value will be inserted. | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
| SET | This data type is similar to ENUM, but SET can have up to 64 list items and can store more than one choice. | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
MySQL Data Types: Date and Time Types
This data type enables us to mention the date and time. MySQL supports the following Date & Time data types.
| Data Type | Description | Storage Required Before MySQL 5.6.4 | Storage Required as of MySQL 5.6.4 |
| YEAR() | Holds the value of year either in a two digit or in a four-digit format. Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069) | 1 byte | 1 byte |
| DATE() | Holds the date values in the format: YYYY-MM-DD, where the supported range is (1000-01-01) to (9999-12-31) | 3 bytes | 3 bytes |
| TIME() | Holds the time values in the format: HH:MI:SS, where the supported range is (-838:59:59) to (838:59:59) | 3 bytes | 3 bytes + fractional seconds storage |
| DATETIME() | A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ | 8 bytes | 5 bytes + fractional seconds storage |
| TIMESTAMP() | Holds values which are stored as the number of seconds, with the format (YYYY-MM-DD HH:MI: SS). The supported range is from (1970-01-01 00:00:01) UTC to (2038-01-09 03:14:07) UTC | 4 bytes | 4 bytes + fractional second storage |
MySQL Data Types: Using Data Types from Other Database Engines
If you want to implement the code written by other vendors in SQL, then, MySQL facilitates this by mapping data types. Refer to the following table.
| Other Vendor Type | MySQL Type |
| BOOL | TINYINT |
| BOOLEAN | TINYINT |
| CHARACTER VARYING(M) | VARCHAR(M) |
| FIXED | DECIMAL |
| FLOAT4 | FLOAT |
| FLOAT8 | DOUBLE |
| INT1 | TINYINT |
| INT2 | SMALLINT |
| INT3 | MEDIUMINT |
| INT4 | INT |
| INT8 | BIGINT |
| LONG VARBINARY |
MEDIUMBLOB |
| LONG VARCHAR | MEDIUMTEXT |
| LONG | MEDIUMTEXT |
| MIDDLEINT | MEDIUMINT |
| NUMERIC | DECIMAL |
After this blog on MySQL Data Types, we will be getting into connecting databases with PHP, but before that, you can refer to this SQL Interview Questions blog to know the top questions asked in interviews. Stay tuned!
Published at DZone with permission of Sahiti Kappagantula, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments