OceanBase Database stores data of different numeric data types in the same way. However, the database still provides four storage types that differ in the value range, as described in the following table.
| Storage type | Default storage rule for syntax in MySQL mode | Default storage rule for syntax in Oracle mode | Description |
|---|---|---|---|
| Fixed-point DECIMAL(M,D) = {10^{-D}*i | i = 0,1,...,10^M-1}, where M is a natural number and D is an integer |
|
|
If and only if D1 <= D2 and M1-D1 <= M2-D2, fixed-point numbers (M1,D1) are a subset of fixed-point numbers (M2,D2). |
| Unsigned fixed-point DECIMAL(M,D) |
|
Not supported | An unsigned fixed-point number is equivalent to a non-negative fixed-point number in DECIMAL(M,D). |
| Floating-point NUMBER | Not supported | NUMBER | In Oracle mode of OceanBase Database, floating-point NUMBER indicates floating-point numbers with a precision of 38 bits and must not be a subset of NUMBER(M, D). If and only if M-D <= 38, NUMBER(M, D) is its subset. |
| Oracle FLOAT(P) | Not supported |
|
N/A |
The following table describes the rules for conversion between all numeric data types in OceanBase Database V4.2.0 and later. A hyphen (-) indicates a situation that involves cross-mode conversion.
Notice
Floating-point
NUMBERand OracleFLOATexist only in Oracle mode, while unsigned fixed-pointDECIMAL(M,D)exists only in MySQL mode.In Oracle mode, not allowing instant conversion (that modifies only the schema but does not convert data) means that conversion is not supported for non-empty tables. In MySQL mode, not allowing instant conversion means that data must be rewritten. Even if instant conversion is allowed in MySQL mode, the database does not ensure that data of all source types can be inserted into the destination type without loss. For example, lossless conversion cannot be guaranteed if you convert fixed-point numbers to Oracle
FLOATnumbers.
| Allow instant conversion (from\to) | Fixed-point DECIMAL(M2,D2) | Unsigned fixed-point DECIMAL(M2,D2) | Floating-point NUMBER | Oracle FLOAT(P2) |
|---|---|---|---|---|
| Fixed-point DECIMAL(M1,D1) | Condition for conversion: D1 <= D2 and M1-D1 <= M2-D2 | Not supported | Condition for conversion: M-D <= 38 | Condition for conversion: M1-D1 <= ceil(P*0.30103) |
| Unsigned fixed-point DECIMAL(M1,D1) | Condition for conversion: D1 <= D2 and M1-D1 <= M2-D2 | Condition for conversion: D1 <= D2 and M1-D1 <= M2-D2 | - | - |
| Floating-point NUMBER | Not supported | - | Supported | Not supported |
| Oracle FLOAT(P1) | Not supported | - | Supported | Condition for conversion: P1 <= P2 |