The storage format of the NUMBER type in OceanBase Database is the same, but it can be categorized into four different formats based on the value domain, as shown in the following table.
| Storage type | Default storage rules in MySQL mode | Default storage rules in Oracle mode | Description |
|---|---|---|---|
Fixed-point number DECIMAL(M,D) = {10^{-D}*i | i = 0,1,...,10^M-1} where M is a natural number and D is an integer |
|
|
A fixed-point number (M1,D1) is a subset of a fixed-point number (M2,D2) if and only if D1 <= D2 and M1-D1 <= M2-D2. |
| Unsigned fixed-point number DECIMAL(M,D) |
|
Not supported | An unsigned fixed-point number is equivalent to the non-negative values of the fixed-point number DECIMAL(M,D). |
| Floating-point NUMBER | Not supported | NUMBER | In Oracle mode, the floating-point NUMBER has a total precision of 38 bits and is not a subset of NUMBER(M, D). However, NUMBER(M, D) is a subset of the floating-point NUMBER if and only if M-D <= 38. |
| Oracle FLOAT(P) | Not supported |
|
Not applicable. |
The conversion rules between all NUMBER types in OceanBase Database V4.2.0 and later are as follows. "-" indicates that the conversion spans compatibility modes.
Notice
Floating-point
NUMBERand OracleFLOATexist only in Oracle mode, while unsigned fixed-point numbers exist only in MySQL mode.In Oracle mode, instant conversion (schema-only conversion without data changes) is not allowed for non-empty tables. In MySQL mode, instant conversion is not allowed because even if it is allowed, not all source data can be losslessly inserted into the target type, for example, converting a fixed-point number to Oracle
FLOAT.
| Instant conversion allowed (From\To) | Fixed-point number (M2,D2) | Unsigned fixed-point number (M2,D2) | Floating-point NUMBER | Oracle FLOAT(P2) |
|---|---|---|---|---|
| Fixed-point number (M1,D1) | Conversion condition: D1 <= D2 and M1-D1 <= M2-D2 | Not supported | Conversion condition: M-D <= 38 | Conversion condition: M1-D1 <=ceil(P*0.30103) |
| Unsigned fixed-point number (M1,D1) UNSIGNED | Conversion condition: D1 <= D2 and M1-D1 <= M2-D2 | Conversion condition: D1 <= D2 and M1-D1 <= M2-D2 | - | - |
| Floating-point NUMBER | Not supported | - | Supported | Not supported |
Oracle FLOAT(P1) |
Not supported | - | Supported | Conversion condition: P1 <= P2 |