SQL Data types explained
Whenever a table is created the columns composing it are assigned to a Data type.
1) Numeric formats :
In the numeric types, the size parameter represents the maximum number of digits in your number.
For example, a SMALLINT(3) value can display 531 but won’t display 22999.
| SMALLINT (size) | Signed : From 0 to 65535. |
| Unsigned : From -32768 to 32767. | |
| INTEGER (size) | Signed : From 0 to 4294967295. |
| Unsigned : From -2147483648 to 2147483647. | |
| BIGINT (size) | Signed : From 0 to 18 446 744 073 709 551 615. |
| Unsigned : From -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807. | |
| BOOL | 0 is equal to False and any other non-zero values (like 1) are equal to True. |
For the decimal types the size parameter indicates the total number of digits. In addition we also have define the number of digits we want after the decimal point (parameter d).
| DECIMAL (size, d) | From -10^38 +1 to 10^38 -1. |
| REAL | From −3.40E+38 to 3.40E+38. |
| FLOAT (size, d) | From -1.79E + 308 to 1.79E + 308. |
2) Character string formats :
| CHAR (size) | Fixed size string. All values of this column type must have the exact length. Size can be defined from 0 to 255 characters. |
| VARCHAR (size) | Variable size string. All values of this column can have varying length going up to the size value.Size can be defined from 0 to 65535 characters. |
| TEXT (size) | Strings with varying length defined where the size defines the maximum number of bytes. Size can be defined from 0 to 65535 bytes. |
| LONGTEXT | Strings with a varying length going up to 4 294 967 295 characters. |
The CHAR and VARCHAR are using “regular” characters letters which we use in the english language. However some languages do not use the same letters, like chinese. For this kind of language we may need to use different data type named GRAPHIC. Indeed, the characters 漢字 are what we can call graphic. A GRAPHIC character takes 2 bytes instead of only 1 for CHAR.
| GRAPHIC (size) | Fixed size string. All values of this column type must have the exact length. Size can be defined from 0 to 127 characters. |
| VARGRAPHIC (size) | Variable size string. All values of this column can have varying length going up to the size value.Size can be defined from 0 to 16352 characters. |
There are also some other text data types for very long string like books or paper. You may indeed store a complete paper content into this kind of data.
| CLOB – Character Large Object | Large text holding up to 2Gb. |
| DBCLOB – Double Bytes Character Large Object | Similar to CLOB but for GRAPHIC texts (i.e. for Chinese texts). |
3) Date/time formats:
| DATE | Stores date in the format YYYY-MM-DD |
| DATETIME | Stores date and time information in the format YYYY-MM-DD HH:MI:SS |
| TIME | Stores time in the format HH:MI:SS |
| TIMESTAMP | Stores number of seconds passed since the Unix epoch (‘1970-01-01 00:00:00’ UTC) |
| YEAR | 4 digits format : range from 1901 to 2155 2 digits format Range 70 to 69 (representing 1970 to 2069) |
4) Binaries formats :
| BINARY | Fixed binary with maximum length of 8000 bytes. |
| VARBINARY | Variable binary length going up to 8000 bytes. |
| BLOB – Binary Large Object | Large binary (image, audio, video…) |
