The floating-point data types are used to store imprecise numeric values with fixed lengths. The value range and precision of a floating-point data type depend on the precision and scale parameters and whether the data type is unsigned.
Precision specifies the total maximum number of valid digits and scale specifies the maximum number of valid digits in the fractional part. You can obtain the maximum number of valid digits in the integer part by subtracting the scale from the precision. The maximum value of the precision is 255 and that of the scale is 30. When the precision is 255, the scale must be 0.
Note
The precision of a floating-point data type is only a theoretical value specified in the IEEE standard and is subject to hardware or operating system limitations.
The following table describes the length and value range of each floating-point data type when the precision and scale parameters are not specified.
Type |
Length (bytes) |
Value range |
Precision (digits) |
|---|---|---|---|
FLOAT |
4 | [-3.402823466E+38,-1.175494351E-38], 0, and [1.175494351E-38,3.402823466E+38] | 7 |
DOUBLE |
8 | [-1.7976931348623157E+308,-2.2250738585072014E-308], 0, and [2.2250738585072014E-308,1.7976931348623157E+308] | 15 |
If the precision and scale parameters are specified, the value range is determined in the same way as that for the fixed-point data types.
FLOAT
The FLOAT type is used to represent a small (single-precision) floating-point number. The syntax is as follows:
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
M specifies the total number of digits in the value that can be stored, and D specifies the number of digits after the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column. If a column is defined as UNSIGNED, the column values cannot be negative.
Note
The FLOAT(M,D) syntax is deprecated. We do not recommend that you use this syntax.
The following FLOAT syntax is also supported:
FLOAT(p) [UNSIGNED] [ZEROFILL]
p specifies the precision in bits. It is used only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT, without the M or D value. If p is from 25 to 53, the data type becomes DOUBLE, without the M or D value. The range of the resulting column is the same as that of the FLOAT or DOUBLE data type described in the preceding table.
DOUBLE
The DOUBLE data type is used to represent a normal-sized (double-precision) floating-point number. The syntax is as follows:
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
M specifies the total number of digits in the value that can be stored, and D specifies the number of digits after the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column. If a column is defined as UNSIGNED, the column values cannot be negative.
Note
The DOUBLE[(M,D)] syntax is deprecated in the latest MySQL release. We recommend that you do not use this syntax. If you need to query exact values, use the DECIMAL data type.
DOUBLE PRECISION
The DOUBLE PRECISION data type is a synonym for the DOUBLE data type. The syntax is as follows:
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]