All Products
Search
Document Center

Numeric data types

Last Updated: Jun 18, 2021

Numeric data types can be divided into three types:

  • Integer types: BOOL/BOOLEAN, TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT

  • Fixed-point types: DECIMAL and NUMERIC

  • Floating-point types: FLOAT and DOUBLE

  • Bit-Value type: BIT

You can use the UNSIGNED keyword to declare all data types as unsigned types, which changes the value ranges changes of the data types accordingly.

You can set the precision and scale when defining the numeric data types. The meanings of precision and scale differ for different data types.

Integer types

The integer types are characterized by fixed length and precise values. The value range of an integer type depends on the type length and whether the data type is unsigned. The precision parameter only specifies the minimum display width. For more information, see ZEROFILL attribute. The following table lists the relevant information.

Type

Length (bytes)

Value range (signed)

Value range (unsigned)

BOOL/BOOLEAN/

TINYINT

1

[-27, 27 - 1]

[0, 28 - 1]

SMALLINT

2

[-215, 215 - 1]

[0, 216 - 1]

MEDIUMINT

3

[-223, 223 - 1]

[0, 224 - 1]

INT/INTEGER

4

[-231, 231 - 1]

[0, 232 - 1]

BIGINT

8

[-263, 263 - 1]

[0, 264 - 1]

BOOL is equivalent to BOOLEAN, and these two types are equivalent to TINYINT(1).

INT is equivalent to INTEGER.

Fixed-point types

The fixed-point types are characterized by variable length and precise values. The value range and precision of a fixed-point type depend on the precision and scale parameters and whether the data type is unsigned. The precision and scale parameters respectively specify the total maximum number of valid digits and the maximum number of valid digits in the fractional part. The maximum number of valid digits in the integer part is the value of precision minus scale. The maximum values of precision and scale are 65 and 30, and the default values are 10 and 0 respectively. Example:

  • For DECIMAL(5, 2), the maximum numbers of valid digits in the integral part and the fractional part are 3 and 2 respectively, so its value range is [-999.99, 999.99].

  • If it is defined as UNSIGNED, its value range is [0, 999.99].

DECIMAL is equivalent to NUMERIC.

Floating-point types

The floating-point types are characterized by fixed length and precise values. The value range and precision of a floating-point type depend on the precision and scale parameters and whether the data type is unsigned. The precision and scale parameters respectively specify the total maximum number of valid digits and the maximum number of valid digits in the fractional part. The maximum number of valid digits in the integer part is the value of precision minus scale. The maximum values of precision and scale are 53 and 30 respectively.

The precision of a floating-point type is only a theoretical value specified in the IEEE standard and is subject to hardware or operating system limits.

The following table lists the default information when precision and scale are not specified.

Type

Length (bytes)

Value range (signed)

Value range (unsigned)

Precision

FLOAT

4

[-2128, 2128]

[0, 2128]

7 digits

DOUBLE

8

[-21024, 21024]

[0, 21024]

15 digits

If precision and scale are specified, the value range is determined in the same way as that for the fixed-point types.

BIT-Value type

The BIT data type is used to store bit values. A BIT(M) can store an M-bit value. The value range of M is 1 to 64.

The bit value is specified in the format of b'value'. value is represented using 0 and 1. For example, b'111' indicates 7, and b'10000000' indicates 128.

When you insert a value into the BIT(M) column, if the length of the inserted value is less than M, 0 is filled on the left side. For example, if b'101' is inserted into BIT(6), it is equivalent to inserting b'000101'.

ZEROFILL attribute

You can use the ZEROFILL keyword to specify the minimum display width of the numeric type and implicitly define the type as UNSIGNED. If the actual data display width is insufficient for the minimum display width, 0 is filled to the fractional part and integer part to reach the upper bounds of scale and precision respectively, so that the minimum display width is reached. Examples:

  • INT(5) ZEROFILL: Data value 123 is displayed as 00123.

  • DECIMAL(10, 5) ZEROFILL: Data value 123.456 is displayed as 00123.45600.