When you create a table, you must specify a data type for each column in the table. A data type defines the type of data that can be stored in a column. For example, a DATE column can store values like "2020-02-20" but cannot store numbers such as 2 or strings such as "hello".
For more information about SQL data types, see SQL Reference (MySQL mode). The following table describes some common data types.
Category |
Type |
Remarks |
|---|---|---|
| Numeric data types - integer | BIGINT | Signed range: [-2^63, 2^63 - 1] Unsigned range: [0, 2^64 - 1] |
| Numeric data types - integer | INT INTEGER | Signed range: [-2^31, 2^31-1] Unsigned range: [0, 2^32-1] |
| Numeric data types - integer | SMALLINT | Signed range: [-2^15, 2^15-1] Unsigned range: [0, 2^16-1] |
| Numeric data types - integer | BOOL BOOLEAN TINYINT | Signed range: [-2^7, 2^7-1] Unsigned range: [0, 2^8-1] |
| Numeric data types - fixed-point | DECIMAL(p,s) | DECIMAL is equivalent to NUMERIC. |
| Numeric data types - floating-point | FLOAT | Signed range: [-2^128, 2^128] Unsigned range: [-2^1024, 2^1024] 7 digits of precision |
| Numeric data types - floating-point | DOUBLE | Signed range: [-2^1024, 2^1024] Unsigned range: [0, 2^1024] 15 digits of precision |
| Numeric data types - integer/fixed-point/floating-point | number number(p) number(p, s) | p stands for precision and s stands for scale, the number of digits after the decimal point. The maximum precision is 38 and the scale can range from -84 to 127. * A value with both precision and scale is a fixed-point value. * A value with a scale of 0 is an integer value. * A value without precision and scale is a floating-point value with a maximum precision of 38. |
| Character data types - variable-length | VARCHAR(N) | Maximum length: 256 KB. Character set: UTF8MB4. |
| Character data types - variable-length | VARBINARY | Maximum length: 256 KB. Character set: BINARY. |
| Character data types - variable-length | enum | Maximum number of elements: 65,535. Maximum length of each element: 255 characters. Character set: UTF8MB4. |
| Character data types - variable-length | set | Maximum number of elements: 64. Maximum length of each element: 255 characters. Character set: UTF8MB4. |
| Character data types - fixed-length | CHAR(N) | Maximum length: 256 KB. Character set: UTF8MB4. |
| Character data types - fixed-length | binary | Maximum length: 256 KB. Character set: BINARY. |
| Date and time data types | DATE | The DATE type is used for values that contain only a date. The DATE values are retrieved and displayed in YYYY-MM-DD format. |
| Date and time data types | TIME | The TIME type is used for values that contain only a time. The TIME values are retrieved and displayed in HH:MM:SS[.fraction] format. |
| Date and time data types | DATETIME | The DATETIME type is used for values that contain both a date and a time (exclusive of the time zone). The DATETIME values are retrieved in YYYY-MM-DD HH:MM:SS[.fraction] format. |
| Date and time data types | TIMESTAMP | The TIMESTAMP data type is used for values that contain both date and time. |
| Date and time data types | YEAR | Format: YYYY. Range: [1901,2155] |
| Large-object data types | TEXT/BLOB | Maximum length: 64 KB |
| Large-object data types | LONGTEXT/LONGBLOB | Maximum length: 48 MB |