Data Types in SQL
Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column.
Data types mainly classified into three categories for every database.
String Data types
Numeric Data types
Date and time Data types
MySQL String Data Types
| Data Type | Description |
|---|---|
| CHAR(Size) | It is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can be 0 to 255 characters. Default is 1. |
| VARCHAR(Size) | It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters. |
| BINARY(Size) | It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. Default is 1. |
| VARBINARY(Size) | It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes. |
| TEXT(Size) | It holds a string that can contain a maximum length of 255 characters. |
| TINYTEXT | It holds a string with a maximum length of 255 characters. |
| MEDIUMTEXT | It holds a string with a maximum length of 16,777,215. |
| LONGTEXT | It holds a string with a maximum length of 4,294,967,295 characters. |
| ENUM(val1, val2, val3,…) | It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
| SET( val1,val2,val3,….) | It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list. |
| BLOB(size) | It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes. |
MySQL Numeric Data Types
| Data Type | Description |
|---|---|
| BIT(Size) | It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1. |
| INT(size) | It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width that is 255. |
| INTEGER(size) | It is equal to INT(size). |
| FLOAT(size, d) | It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by d parameter. |
| FLOAT(p) | It is used to specify a floating point number. MySQL used p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE(). |
| DOUBLE(size, d) | It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by d parameter. |
| DECIMAL(size, d) | It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0. |
| DEC(size, d) | It is equal to DECIMAL(size, d). |
| BOOL | It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. |
MySQL Date and Time Data Types
| Data Type | Description |
|---|---|
| DATE | It is used to specify date format YYYY-MM-DD. Its supported range is from ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(fsp) | It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000-01-01 00:00:00’ to 9999-12-31 23:59:59’. |
| TIMESTAMP(fsp) | It is used to specify the timestamp. Its value is stored as the number of seconds since the Unix epoch(‘1970-01-01 00:00:00’ UTC). Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. |
| TIME(fsp) | It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR | It is used to specify a year in four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000. |