Numeric Types
TiDB supports all the MySQL numeric types, including:
- Integer Types (Exact Value)
- Floating-Point Types (Approximate Value)
- Fixed-Point Types (Exact Value)
Integer types
TiDB supports all the MySQL integer types, including INTEGER/INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT. For more information, see Integer Data Type Syntax in MySQL.
The following table summarizes field descriptions:
| Syntax Element | Description |
|---|---|
| M | the display width of the type. Optional. |
| UNSIGNED | UNSIGNED. If omitted, it is SIGNED. |
| ZEROFILL | If you specify ZEROFILL for a numeric column, TiDB automatically adds the UNSIGNED attribute to the column. |
The following table summarizes the required storage and range for integer types supported by TiDB:
| Data Type | Storage Required (bytes) | Minimum Value (signed/unsigned) | Maximum value (signed/unsigned) |
|---|---|---|---|
TINYINT | 1 | -128 / 0 | 127 / 255 |
SMALLINT | 2 | -32768 / 0 | 32767 / 65535 |
MEDIUMINT | 3 | -8388608 / 0 | 8388607 / 16777215 |
INT | 4 | -2147483648 / 0 | 2147483647 / 4294967295 |
BIGINT | 8 | -9223372036854775808 / 0 | 9223372036854775807 / 18446744073709551615 |
BIT type
The BIT data type. A type of BIT(M) enables the storage of M-bit values. M can range from 1 to 64, with the default value of 1:
BIT[(M)]
BOOLEAN type
The BOOLEAN type and its alias BOOL are equivalent to TINYINT(1). If the value is 0, it is considered as False; otherwise, it is considered True. As in MySQL, True is 1 and False is 0:
BOOLEAN
TINYINT type
The TINYINT data type stores signed values of range [-128, 127] and unsigned values of range [0, 255]:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINT type
The SMALLINT data type stores signed values of range [-32768, 32767], and unsigned values of range [0, 65535]:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINT type
The MEDIUMINT data type stores signed values of range [-8388608, 8388607], and unsigned values of range [0, 16777215]:
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INTEGER type
The INTEGER type and its alias INT stores signed values of range [-2147483648, 2147483647], and unsigned values of range [0, 4294967295]:
INT[(M)] [UNSIGNED] [ZEROFILL]
You can also use another form:
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
BIGINT type
The BIGINT data type stores signed values of range [-9223372036854775808, 9223372036854775807], and unsigned values of range [0, 18446744073709551615]:
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Floating-point types
TiDB supports all the MySQL floating-point types, including FLOAT, and DOUBLE. For more information, see Floating-Point Types (Approximate Value) - FLOAT, DOUBLE in MySQL.
The following table summarizes field descriptions:
| Syntax Element | Description |
|---|---|
| M | the total number of digits |
| D | the number of digits following the decimal point |
| UNSIGNED | UNSIGNED. If omitted, it is SIGNED. |
| ZEROFILL | If you specify ZEROFILL for a numeric column, TiDB automatically adds the UNSIGNED attribute to the column. |
The following table summarizes the required storage for floating-point types supported by TiDB:
| Data Type | Storage Required (bytes) |
|---|---|
FLOAT | 4 |
FLOAT(p) | If 0 <= p <= 24, it is 4; if 25 <= p <= 53, it is 8 |
DOUBLE | 8 |
FLOAT type
The FLOAT type stores a single-precision floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
FLOAT(p) can be used to represent the required precision in bits. TiDB uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data type.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
FLOAT(p) [UNSIGNED] [ZEROFILL]
DOUBLE type
The DOUBLE type, and its alias DOUBLE PRECISION stores a double-precision floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION [(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
Fixed-point types
TiDB supports all the MySQL floating-point types, including DECIMAL, and NUMERIC. For more information, Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC in MySQL.
The meaning of the fields:
| Syntax Element | Description |
|---|---|
| M | the total number of digits |
| D | the number of digits after the decimal point |
| UNSIGNED | UNSIGNED. If omitted, it is SIGNED. |
| ZEROFILL | If you specify ZEROFILL for a numeric column, TiDB automatically adds the UNSIGNED attribute to the column. |
DECIMAL type
DECIMAL and its alias NUMERIC stores a packed "exact" fixed-point number. M is the total number of digits (the precision, the number of integer digits + the number of decimal digits), and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]