A Data Type specifies a particular type of data, like integer, floating points, Boolean, etc. It also identifies the possible values for that type, the operations that can be performed on that type, and the way the values of that type are stored. In MySQL, each database table has many columns and contains specific data types for each column.
Numeric Data Type
MySQL has all essential SQL numeric data types. These data types can include the exact numeric data types (For example, integer, decimal, numeric, etc.), as well as the approximate numeric data types (For example, float, real, and double precision). It also supports BIT data types to store bit values. In MySQL, numeric data types are categories into two types, either signed or unsigned except for bit data type.
The following table contains all numeric data types that support in MYSQL:
| Data Type Syntax | 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. |
Date and Time Data Type:
This data type is used to represent temporal values such as date, time, datetime, timestamp, and year. Each temporal type contains values, including zero. When we insert the invalid value, MySQL cannot represent it, and then zero value is used.
The following table illustrates all date and time data types that support in MySQL:
| Data Type Syntax | 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. |
String Data Types:
The string data type is used to hold plain text and binary data, for example, files, images, etc. MySQL can perform searching and comparison of string values based on the pattern matching such as LIKE operator, Regular Expressions, etc.
The following table illustrates all string data types that support in MySQL:
| Data Type Syntax | 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. |
anand.johari1994