Overview

OceanBase Database supports all standard SQL numeric data types, which are exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), approximate numeric data types (FLOAT and DOUBLE), the BIT data type that stores bit values, and extended data types (TINYINT, MEDIUMINT, BIGINT, and SERIAL).

Categories of numeric data types

OceanBase Database supports the following four categories of numeric data types:

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

• Fixed-point types: DECIMAL and NUMERIC

• Floating-point types: FLOAT and DOUBLE

• Bit-value type: BIT

You can set the precision and scale when you define a numeric data type. The precision indicates the length of a value. The scale indicates the number of decimal places in a value. However, the meanings of the precision and scale may vary with data types.

ZEROFILL attribute

You can use the ZEROFILL keyword to specify the minimum display width of a numeric data type, and implicitly define the type as UNSIGNED. If the actual display width of data is less than the minimum display width, the fractional part is first padded with zeros to reach the maximum scale, and the integer part is then padded with zeros to reach the maximum precision. This way, the minimum display width is reached.

Here are some examples:

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

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