Notice
If you use a DECIMAL type column as the partitioning key and use OceanBase Database Proxy (ODP) to connect to OceanBase Database, you must use ODP V4.2.3 or later to ensure accurate routing.
The fixed-point data types are used to store precise numeric values with variable lengths. The value range and precision of a fixed-point data type depend on the precision and scale parameters and whether the data type is unsigned.
DECIMAL is equivalent to NUMERIC. The syntax is as follows:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
Here, M specifies the precision, which is the total number of digits that can be stored. D specifies the scale, which is the number of decimal places. The decimal point (.) and minus sign (-) are not counted in M. If D is 0, the value does not have the decimal point or fractional part. The result of subtracting D from M is the maximum number of valid digits in the integer part. When neither M nor D is specified, the precision is 65 digits for the results of all basic operations (addition, subtraction, multiplication, and division) involving a DECIMAL column.
In the DECIMAL syntax, the maximum value of M is 65, and the maximum value of D is 30. If D is not specified, the value of D is 0. If M is not specified, the value of M is 10.
If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column. If a column is defined as UNSIGNED, the column values cannot be negative.
For example, DECIMAL(5,2) indicates that the maximum number of valid digits in the integral part is 3 and that in the fractional part is 2, so the value range is [-999.99,999.99]. If you specify the UNSIGNED attribute, the value range is [0,999.99].
Data types in the following syntax are synonyms for the DECIMAL data type. Among them, FIXED is compatible with other database systems.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]