Introduction
In a database, each column of a table has a specific data type. A data type specifies the type of data that column can hold such as character strings, numeric values, and date time values. SQL supplies a set of basic data types that you can use for defining columns of tables.
Classification
Data types mainly classified into three categories
- String
- Numeric
- Date and Time
String Data Types
- CHAR(Size) : It is used to specify a fixed length string that can contain numbers, letters, and special characters.
- VARCHAR(Size): It is used to specify a variable length string that can contain numbers, letters, and special characters.
- BINARY(Size): It is equal to CHAR() but stores binary byte strings.
- VARBINARY(Size) : It is equal to VARCHAR() but stores binary byte strings.
- 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: It is a string object whose value is chosen from a list of permitted values defined at the time of column creation.
Numeric Data Types
- BIT(Size): It is used for a bit-value type. The number of bits per value is specified in size.
- 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.
- BOOL: It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.
Date and Time Data Types
- 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.
- 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.
- TIME(fsp): It is used to specify the time format. Its format is hh:mm:ss.
- YEAR: It is used to specify a year in four-digit format.