The floating-point data type is a fixed-length, inexact numeric type. Its range and precision depend on the type length, Precision and Scale attributes, and whether it is unsigned.
Note
Floating-point numbers are inexact values and may suffer from rounding errors during computation. Different computation orders (including parallel execution and two-stage aggregation), or comparisons after type conversion, can lead to uncertain results. If high precision is required for numerical values, it is preferable to use fixed-point types.
Precision and scale indicate the maximum number of significant digits in total, the maximum number of significant digits in the decimal part, and the maximum number of significant digits in the integer part, respectively. The maximum value of precision is 255 (when scale can only be 0), and the maximum value of scale is 30.
Note
The precision of the floating-point type is only a theoretical value specified in the IEEE standard. In practice, it may vary slightly due to hardware or operating system limitations.
The following table describes the storage length and value range required for floating-point types when Precision and Scale are not specified.
Type |
Length (bytes) |
Range |
Accuracy |
|---|---|---|---|
FLOAT |
4 | [-3.402823466E+38, -1.175494351E-38], 0 and [1.175494351E-38,3.402823466E+38] | 7 bits |
DOUBLE |
8 | [-1.7976931348623157E+308, -2.2250738585072014E-308], 0 and [2.2250738585072014E-308,1.7976931348623157E+308] | 15 bits |
If Precision and Scale are specified, the range of values is determined in the same way as for fixed-point types.
FLOAT
FLOAT is used to represent a small (single-precision) floating-point number. The syntax is as follows:
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
M is the total number of bits that can be stored, and D is the number of decimal places. If M and D are omitted, the value is stored within the limit allowed by the hardware. A single-precision floating-point number is accurate to about 7 decimal places.
If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to that column. If UNSIGNED is specified, negative values are not allowed.
Note
FLOAT(M,D) is an obsolete syntax and is not recommended for use.
The FLOAT data type also supports the following syntax:
FLOAT(p) [UNSIGNED] [ZEROFILL]
p specifies the precision in bits, but this value is used only to determine whether the result data type is FLOAT or DOUBLE. If p is 0 to 24, the data type is FLOAT without an M or D value. If p is 25 to 53, the data type is DOUBLE without an M or D value. The range of the result column is the same as that of the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
DOUBLE
DOUBLE is used to represent normal-sized (double-precision) floating-point numbers. The syntax is as follows:
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
M is the total number of digits that can be stored, and D is the number of decimal places. If M and D are omitted, the value is stored within the range allowed by the hardware. A double-precision floating-point number is accurate to about 15 decimal places.
If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to that column. If UNSIGNED is specified, negative values are not allowed.
Note
DOUBLE[(M,D)] is an obsolete syntax in MySQL and is not recommended for use. If precise matching is required, it is recommended to use the DECIMAL type.
DOUBLE PRECISION
DOUBLE PRECISION is a synonym for DOUBLE. The syntax is as follows:
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
Note
Binary floating-point numbers do not currently support the special values infinity and NaN.