MySQL Tutorial
Data types are the classification of data items.
Data Types are used to represent the nature of the data that can be stored in the database table.
MySQL Data Type is an attribute that specifies the type of data of any object.
While creating your tables you can use MySQL Data Type.
MySQL uses many different data types broken into three categories −
A brief idea of all the data types are discussed below.
MySQL has all essential SQL numeric data types.
These data types can include the exact numeric data types as well as the approximate numeric data types
The following table contains all numeric data types that support in MySQL:
| Data Type | Description |
|---|---|
| TINYINT | It is a very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. We can specify a width of up to 4 digits. It takes 1 byte for storage. |
| SMALLINT | It is a small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. We can specify a width of up to 5 digits. It requires 2 bytes for storage. |
| MEDIUMINT | It is a medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. We can specify a width of up to 9 digits. It requires 3 bytes for storage. |
| INT | It is a normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. We can specify a width of up to 11 digits. It requires 4 bytes for storage. |
| BIGINT | It is a large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. We can specify a width of up to 20 digits. It requires 8 bytes for storage. |
| FLOAT(m,d) | It is a floating-point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 10,2, where 2 is the number of decimals, and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a float type. It requires 2 bytes for storage. |
| DOUBLE(m,d) | It is a double-precision floating-point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a double. Real is a synonym for double. It requires 8 bytes for storage. |
| DECIMAL(m,d) | An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. Defining the display length (m) and the number of decimals (d) is required. Numeric is a synonym for decimal. |
| BIT(m) | It is used for storing bit values into the table column. Here, M determines the number of bit per value that has a range of 1 to 64. |
| BOOL | It is used only for the true and false condition. It considered numeric value 1 as true and 0 as false. |
| BOOLEAN | It is Similar to the BOOL. |
This data type is used to represent temporal values such as date, time, datetime, timestamp, and year. Each temporal type contains values
The following table illustrates all date and time data types that support in MySQL:
| Data Type | Maximum Size | Explanation |
|---|---|---|
| YEAR[(2|4)] | Year value as 2 digits or 4 digits. | The default is 4 digits. It takes 1 byte for storage. |
| DATE | Values range from '1000-01-01' to '9999-12-31'. | Displayed as 'yyyy-mm-dd'. It takes 3 bytes for storage. |
| TIME | Values range from '-838:59:59' to '838:59:59'. | Displayed as 'HH:MM:SS'. It takes 3 bytes plus fractional seconds for storage. |
| DATETIME | Values range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. | Displayed as 'yyyy-mm-dd hh:mm:ss'. It takes 5 bytes plus fractional seconds for storage. |
| TIMESTAMP(m) | Values range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' TC. | Displayed as 'YYYY-MM-DD HH:MM:SS'. It takes 4 bytes plus fractional seconds for storage. |
The string data type is used to hold plain text and binary data
The following table illustrates all string data types that support in MySQL
| Data Type | Maximum Size | Explanation |
|---|---|---|
| CHAR(size) | It can have a maximum size of 255 characters. | Here size is the number of characters to store. Fixed-length strings. Space padded on the right to equal size characters. |
| VARCHAR(size) | It can have a maximum size of 255 characters. | Here size is the number of characters to store. Variable-length string. |
| TINYTEXT(size) | It can have a maximum size of 255 characters. | Here size is the number of characters to store. |
| TEXT(size) | Maximum size of 65,535 characters. | Here size is the number of characters to store. |
| MEDIUMTEXT(size) | It can have a maximum size of 16,777,215 characters. | Here size is the number of characters to store. |
| LONGTEXT(size) | It can have a maximum size of 4GB or 4,294,967,295 characters. | Here size is the number of characters to store. |
| BINARY(size) | It can have a maximum size of 255 characters. | Here size is the number of binary characters to store. Fixed-length strings. Space padded on the right to equal size characters. (introduced in MySQL 4.1.2) |
| VARBINARY(size) | It can have a maximum size of 255 characters. | Here size is the number of characters to store. Variable-length string. (introduced in MySQL 4.1.2) |
| ENUM | It takes 1 or 2 bytes that depend on the number of enumeration values. An ENUM can have a maximum of 65,535 values. | It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values. |
| SET | It takes 1, 2, 3, 4, or 8 bytes that depends on the number of set members. It can store a maximum of 64 members. | It can hold zero or more, or any number of string values. They must be chosen from a predefined list of values specified during table creation. |
BLOB in MySQL is a data type that can hold a variable amount of data
The following table shows all Binary Large Object data types that support in MySQL
| Data Type | Maximum Size |
|---|---|
| TINYBLOB | It can hold a maximum size of 255 bytes. |
| BLOB(size) | It can hold the maximum size of 65,535 bytes. |
| MEDIUMBLOB | It can hold the maximum size of 16,777,215 bytes. |
| LONGBLOB | It can hold the maximum size of 4gb or 4,294,967,295 bytes. |
MySQL provides support for native JSON data type from the version v5.7.8.
This data type allows us to store and access the JSON document quickly and efficiently.
