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 type, and extended data types (TINYINT, MEDIUMINT, and BIGINT).

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, and BIGINT

• 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. Precision indicates the length of a value. Scale indicates the number of decimal places in a value. However, the meanings of 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 data value 123 is displayed as 00123.

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