Data types tells about the nature of the data that can be stored in the database table.
SQL data types can be broadly divided into following categories.
- Numeric data types such as int, tinyint, bigint, float, real etc.
- Date and Time data types such as Date, Time, Datetime etc.
- Character and String data types such as char, varchar, text etc.
- Unicode character string data types, for example nchar, nvarchar, ntext etc.
- Binary data types such as binary, varbinary etc.
- Miscellaneous data types – clob, blob, xml, cursor, table etc.
*NOTE:Not all data types are supported by every relational database vendors. For example, Oracle database doesn’t support DATETIME and MySQL doesn’t support CLOB data type.
Let’s have a deep look on all the above datatypes
SQL Character and String Data Types
| Datatype | Description |
|---|---|
| CHAR | Fixed length with maximum length of 8,000 characters |
| VARCHAR | Variable length storage with maximum length of 8,000 characters |
| VARCHAR(max) | Variable length storage with provided max characters, not supported in MySQL |
| TEXT | Variable length storage with maximum size of 2GB data |
SQL Numeric Data Types
| Datatype | From | To |
|---|---|---|
| bit | 0 | 1 |
| tinyint | 0 | 255 |
| smallint | -32,768 | 32,767 |
| int | -2,147,483,648 | 2,147,483,647 |
| bigint | -9,223,372,036, 854,775,808 | 9,223,372,036, 854,775,807 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
SQL Date and Time Data Types
| Datatype | Description |
|---|---|
| DATE | Stores date in the format YYYY-MM-DD |
| TIME | Stores time in the format HH:MI:SS |
| DATETIME | Stores date and time information in the format YYYY-MM-DD HH:MI:SS |
| TIMESTAMP | Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC) |
| YEAR | Stores year in 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit format. Range 70 to 69, representing 1970 to 2069. |
SQL Binary Data Types
| Datatype | Description |
|---|---|
| BINARY | Fixed length with maximum length of 8,000 bytes |
| VARBINARY | Variable length storage with maximum length of 8,000 bytes |
| VARBINARY(max) | Variable length storage with provided max bytes |
| IMAGE | Variable length storage with maximum size of 2GB binary data |
SQL Unicode Character and String Data Types
| Datatype | Description |
|---|---|
| NCHAR | Fixed length with maximum length of 4,000 characters |
| NVARCHAR | Variable length storage with maximum length of 4,000 characters |
| NVARCHAR(max) | Variable length storage with provided max characters |
| NTEXT | Variable length storage with maximum size of 1GB data |
SQL Miscellaneous Data Types
| Datatype | Description |
|---|---|
| CLOB | Character large objets that can hold up to 2GB |
| BLOB | For binary large objects |
| XML | for storing xml data |
| JSON | for storing JSON data |
That’s all for a quick roundup on SQL data types.
Now have a look on the Datatypes that are used in MySQL
MySQL Data Types
A list of data types used in MySQL database. This is based on MySQL 8.0.
MySQL String Data Types
| 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
| 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
| 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. |
