SQL data types are data types that you can use to represent the data nature and store them in the SQL database table. It is basically an attribute that defines the data type of any object. In each column in SQL, expression and variable have a data type related. You can make use of these data types to create necessary tables. You can opt for the necessary data type for a column in your table as per your requirements.
The column of the data type determines the value that a particular column can hold, including integer values, character values, binary values, money values, date and time values, and more.
Uses of SQL
The SQL create table statement allows you to create new tables in the given database. SQL is a beneficial language for data scientists and business analysts. It is also popularly used in the field of data migration. SQL allows users to generate queries and obtain responses to the queries.
Some important tips on data types in SQL:
- Not all relational database vendors support all data types.
- All relation databases have their own size limit for the various distinct data types.
MySQL Data Types
As per MySQL data types W3schools, you can largely divide these data types can into the categories mentioned below:
- String
- Numeric
- Date/Time
String Data Types
You can categorize the string MySQL data types into the following 14 types. Let’s get a brief understanding of them all:
- CHAR (size)
- VARCHAR (size)
- BINARY (size)
- VARBINARY (size)
- TINYBLOB
- TINYTEXT
- TEXT (size)
- BLOB (size)
- MEDIUMTEXT
- MEDIUMBLOB
- LONGTEXT
- LONGBLOB
- ENUM
- SET
CHAR (Size)
This SQL data type is a string of fixed length that may consist of numbers, letters, and special characters. In this data type, the size parameter defines the length of the column in the characters, which can range between 0 and 255. By default, the size is 1.
VARCHAR (size)
This data type has a variable string length that has the capacity to hold numbers, letters, as well as special characters, similar to CHAR, however, with a varying length rather than a fixed one. Here also, the size parameter defines the maximum length of the column in the given characters. This size may range between 0 and 65535.
BINARY (size)
This is similar to CHAR(), although it stores values of binary byte strings. Like the other data types, in this, the size parameter defines the byte length of the column. The default size of this is 1.
VARBINARY (size)
Similar to VARCHAR(), this data type allows you to store values of binary byte strings. In this data type, the size parameter defines the maximum length of the column in bytes.
TINYBLOB
BLOB stands for Binary Large Objects. The maximum length of this data type is 255 bytes.
TINYTEXT
This data type stores a string whose maximum length is of 255 characters.
TEXT (size)
TEXT (size) holds a string that has a maximum length of about 65,535 bytes.
BLOB (size)
BLOBs allow you to store data values that are up to 65,535 bytes in size.
MEDIUMTEXT
This allows you to store string values with up to 16,777,215 characters.
MEDIUMBLOB
This data type stores data with a maximum length of 16,777,215 bytes.
LONGTEXT
LONGTEXT lets you store strings having a maximum length of about 4,294,967,295 characters.
LONGBLOB
This helps you store data values of up to 4,294,967,295 bytes.
ENUM
ENUM (val1, val2, …) is nothing but a string object that can only contain a single value that you can choose from a given list of probable data values. In this list, you have the choice to list a maximum of 65535 data values. If you insert a data value that is not present in the list, it will automatically insert a blank value. Besides, the sorting of these values depends on the order in which you enter them.
SET
SET (val1, val2, …) is a string object that has the capacity to store 0 or more data values that you may take from the oven list containing the possible values. In this list, you have the option to list up to 64 values.
Now that you have read about the String data types let us take a look at the numeric data types this database has to offer.
Numeric Data Types
The numeric data types in MySQL have the categories mentioned below:
- BIT (size)
- TINYINT (size)
- BOOL
- BOOLEAN
- SMALLINT (size)
- MEDIUMINT (size)
- INT (size)
- INTEGER (size)
- BIGINT (size)
- FLOAT (size, d)
- FLOAT(p)
- DOUBLE (size, d)
- DOUBLE PRECISION (size, d)
- DECIMAL (size, d)
- DEC (size, d)
BIT (size)
The size determines the number of bits per given value. The size parameter has the capacity to hold a value between 1 and 64. The default size value, however, is 1.
TINYINT (size)
This can hold an exceptionally small number. The signed value ranges for this is from – 128 to 127. Further, the unsigned range of values is from 0 to 255. Besides, the size parameter determines the maximum width for display, that being 255.
BOOL
This is one of the SQL data types boolean types where the value zero while the non-zero values are true.
BOOLEAN
This data type is similar to BOOL.
SMALLINT (size)
It contains a little number. The signed value ranges for this data type is from – 32768 to 32767. Further, the unsigned range is from 0 to 65535. The size parameter in this indicates the maximum width for display, that being 255.
MEDIUMINT (size)
This holds a medium number. The signed value ranges for this particular data type ranges from – 8388608 to 8388607. Further, the unsigned range is from 0 to 16777215. The size parameter herein indicates t the maximum width for display, 255.
INT (size)
It is a medium value number. The signed value ranges from – 2147483648 to 2147483647, while the unsigned ones range from 0 to 4294967295. The size parameter in this indicates the maximum width for display, which is 255.
INTEGER (size)
This data type is the same as the INT (size) data type.
BIGINT (size)
It contains an enormous number. The signed range for this is from – 9223372036854775808 to 9223372036854775807. However, the unsigned value ranges from 0 to 18446744073709551615. Further, the size parameter indicates the largest display width, 255.
FLOAT (size, d)
This contains a floating-point value. Here, all outnumber of digits uses size for indication. Further, the number of digits after the given, you can determine the decimal point with the parameter d. MySQL 8.0.17 censors this syntax structure, and the future versions of MySQL will eradicate its use.
FLOAT (p)
It is also a floating-point value. MySQL utilizes the p parameter or its value to decide if to utilize FLOAT or use DOUBLE for the subsequent data type. On the off chance that the value of p is from 0 to 24, the data type results as FLOAT(). However, on the off chance that the value of p is from 25 to 53, the data type turns out to be DOUBLE().
DOUBLE (size, d)
A typical-sized floating-point value. The all outnumber of digits uses size for determination. The number of digits after the decimal point makes use of the d parameter for indication.
DECIMAL (size, d)
This is a SQL decimal data type that holds an exact number. The total digit value uses size for definition. The number of digits after the decimal point uses the d parameter for determination. Besides, the most extreme number for size is 65. The most extreme number for d, the decimal, is 30. The default size is 10. The default value for d is 0.
DEC (size, d)
This data type is equal to the data type, DECIMAL (size, d).
Herein, the numeric data types have an extra choice – ZEROFILL or UNSIGNED. In the event that you include the UNSIGNED alternative, MySQL forbids negative qualities for the column. Although, in the event that you include the ZEROFILL alternative, MySQL consequently adds the UNSIGNED attribute to the particular column.
Date & Time Data Types
You can divide this category into the data types mentioned below:
- DATE
- DATETIME (fsp)
- TIMESTAMP (fsp)
- TIME (fsp)
- YEAR
Let’s briefly read about these data types.
DATE
It represents a date in the format: YYYY-MM-DD. The range it supports is between ‘1000-01-01’ and ‘9999-12-31’.
DATETIME (fsp)
It is a combination of both time and date. It follows the format: YYYY-MM-DD hh:mm:ss. Further, the range that this data type supports is between ‘1000-01-01 00:00:00’ and ‘9999-12-31 23:59:59’. Herein, you can also add ON UPDATE and DEFAULT to the column definition to receive automatic initialization, as well as updating the present time and date.
TIMESTAMP (fsp)
It is a timestamp. These values use the number of seconds from the Unix epoch – ‘1970-01-01 00:00:00’ UTC. Its configuration is YYYY-MM-DD hh:mm:ss. Further, it supports the range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. Moreover, the automatic initialization as well as updating to the present date and you can determine the time with the DEFAULT CURRENT_TIMESTAMP and also the ON UPDATE CURRENT_TIMESTAMP in the given definition of the column.
TIME (fsp)
It is a time that follows the format: hh:mm:ss. It supports the range from ‘- 838:59:59′ to ‘838:59:59’.
YEAR
It follows the four-digit format of a year. The values that are data type permits in the four-digit group: 1901 to 2155, and 0000. However, MySQL 8.0 version does not bolster year in two-digit format.
SQL Money Data Type
SQL has the following two data types to store monetary values:
- smallmoney
- money
Smallmoney
This stores monetary data value between -214,748.3648 and 214,748.3647.
Money
This stores monetary data value between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
To learn more about these, you can check out various SQL data types pdf files online and refer them for your benefit.
Oracle SQL data types
Each of the values that the Oracle database manipulates has a specific data type. Further, the data type of these values is generally associated with a particular property set with the given value. With the help of these properties, Oracle differentiates between various data types. Below is a list of Oracle data types:
- VARCHAR2
- NVARCHAR2
- NUMBER
- FLOAT
- LONG
- DATE
- BINARY_FLOAT
- BINARY_DOUBLE
- TIMESTAMP
- INTERVAL YEAR
- INTERVAL DAY
- RAW
- LONG RAW
- ROWID
- UROWID
- CHAR
- NCHAR
- CLOB
- NCLOB
- BLOB
- BFILE
SQLite Data Types
Unlike other SQL database engines that use rigid and static typing, SQLite does not. The container defines the value of any given data type, which is a specific column where you can store the given data value. This database engine uses the general dynamic type system.
The data type of a particular value is in relation to the value itself and not its holder. The dynamic arrangement here is in reverse with the more normal static frameworks of other database motors, as in SQL statements that chip away at statically composed databases should work a similar route in SQLite. Be that as it may, the dynamic composing in SQLite permits it to do things that are unrealistic in traditional composed databases.
PostgreSQL Data Types
The following are the data types that PostgreSQL supports:
- Boolean
- Numeric like floating-point and integer values
- Characters like text, char, and varchar
- UUID
- Array
- JSON
- Temporal such as time, date, interval, and timestamp
- hstore
Conclusion
The main aim of SQL data types blog is to help you come across numerous data types you can use in the SQL database. These data types help you store any value that you need in a specific format so that you can access it using a particular format. From integer values to strings and characters, to floating values, to monetary values, and so on, you can store value in any format you wish. We hope you now have a basic understanding of the plethora of data types the SQL database has to offer and how and when you can use them. To learn more about these, you can refer to various online tutorials, pdfs, presentations, etc. that are on the basis of SQL and its data types.
