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-compatible mode |
Default storage rules in Oracle-compatible 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-compatible 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-compatible mode, while unsigned fixed-point numbers exist only in MySQL-compatible mode.In Oracle-compatible mode, instant conversion (schema-only conversion without data changes) is not allowed for non-empty tables. In MySQL-compatible 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 |