MySQL Datatypes - Overview
An overview of database (tables) datatypes in MySQL server. Used in creation of tables.
Contents
MySQL Data Types: Numeric Types
Numeric Datatypes allow both signed and unsigned integers.
| 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 |
String Data types
This allow both fixed and variable length strings.
| 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) |
Date and Time Types
This data type enables us to mention the date and time.
| 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 |
Outside MYSQL - In other SQL versions
If you want to implement the code written by other vendors in SQL, then, MySQL facilitates this by mapping data types:
| 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 |
