The storage formats of the NUMBER type in OceanBase Database are the same, but they can be classified into four formats based on their value ranges, 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 numbers of the fixed-point number DECIMAL(M,D). |
| Floating-point number NUMBER | Not supported | NUMBER | In Oracle mode, the floating-point NUMBER is a floating-point number with 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 |
|
N/A |
The conversion rules between all NUMBER types in OceanBase Database V4.2.0 and later are as follows. "-" indicates that the conversion is across compatibility modes.
Notice
The floating-point
NUMBERand OracleFLOATtypes exist only in Oracle mode, while the unsigned fixed-point number exists 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 for data rewrite, and even if it is allowed, not all source data can be losslessly inserted into the destination type, for example, from a fixed-point number to an 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 |