Numeric data types |V3.2.4|OceanBase Database| docs|Distributed Database

Numeric data types

Last Updated：2023-10-24 09:23:04 Updated

OceanBase Database supports the following standard SQL numeric data types: exact numeric data types, approximate numeric data types, the BIT data type that stores bit values, and extended data types.

Overview

Data type categories

The current OceanBase Database version supports the following four categories of numeric data types:

Integer data types: BOOL/BOOLEAN/TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT
Fixed-point types: DECIMAL and NUMERIC
Floating-point types: FLOAT and DOUBLE
Bit-value type: BIT

When you define a numeric data type, you can specify the precision and scale for the data type. Precision is the number of digits in a number and scale is the number of digits after the decimal point. The meanings of the precision and scale for different numeric data types may vary from each other.

ZEROFILL attribute

When you define a numeric data type, you can use the ZEROFILL keyword to specify the minimum display width of the numeric type and implicitly define the type as UNSIGNED. If the actual display width of data is less than the minimum display width, the fractional part is first padded with zeros to reach the maximum scale and the integer part is then padded with zeros to reach the maximum precision. This way, the minimum display width is reached.

Example:

INT(5) ZEROFILL: The data value 123 is displayed as 00123.
DECIMAL(10, 5) ZEROFILL: The data value 123.456 is displayed as 00123.45600.

Integer data types

Integer data types are used to store precise numeric values with fixed lengths. The value range of an integer data type depends on the length of the data type and whether the data type is unsigned. The precision of an integer data type indicates the minimum display width.

The following table describes the length and value range of each integer data type supported by OceanBase Database.

Type	Length (bytes)	Value range (signed)	Value range (unsigned)
`BOOL`/`BOOLEAN`/ `TINYINT`	1	[-2^7^, 2^7^ - 1]	[0, 2^8^ - 1]
`SMALLINT`	2	[-2^15^, 2^15^ - 1]	[0, 2^16^ - 1]
`MEDIUMINT`	3	[-2^23^, 2^23^ - 1]	[0, 2^24^ - 1]
`INT`/`INTEGER`	4	[-2^31^, 2^31^ - 1]	[0, 2^32^ - 1]
`BIGINT`	8	[-2^63^, 2^63^ - 1]	[0, 2^64^ - 1]

TINYINT

The TINYINT data type is used to represent a very small integer. Syntax:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

M indicates the maximum display width. The maximum value of M is 255. The display width is independent of the range of values that can be stored. If you specify the ZEROFILL attribute for a numeric column, OceanBase Database automatically adds the UNSIGNED attribute to the column.

BOOL/BOOLEAN

The BOOL/BOOLEAN data type is a synonym for the TINYINT data type. A zero value indicates false, whereas a non-zero value indicates true. Example:

obclient> SELECT IF(0, 'true', 'false');
+------------------------+
 IF(0, 'true', 'false') 
+------------------------+
 false                  
+------------------------+
1 row in set

obclient> SELECT IF(1, 'true', 'false');
+------------------------+
 IF(1, 'true', 'false') 
+------------------------+
 true                   
+------------------------+
1 row in set

obclient> SELECT IF(2, 'true', 'false');
+------------------------+
 IF(2, 'true', 'false') 
+------------------------+
 true                   
+------------------------+
1 row in set

obclient> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
 IF(2 = FALSE, 'true', 'false') 
+--------------------------------+
 false                          
+--------------------------------+
1 row in set

SMALLINT

The SMALLINT data type is used to represent a small integer. Syntax:

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

MEDIUMINT

The MEDIUMINT data type is used to represent a medium-sized integer. Syntax:

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

INT or INTEGER

The INT or INTEGER data type is used to represent a normal-sized integer. Syntax:

INT[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL]

BIGINT

The BIGINT data type is used to represent a large integer. Syntax:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Note:

You must use signed BIGINT or DOUBLE values in all operations. Therefore, you cannot use unsigned integers that are larger than 9223372036854775807 in value or greater than 63 bits in length except in bit() functions. Otherwise, a rounding error may occur when you convert a BIGINT value to a DOUBLE value. As a result, the last digit in the conversion result may be incorrect.
You can store an exact integer in a BIGINT column as a string. In this case, OceanBase Database can convert a string to a number without intermediate double-precision representation.
If both operands are integers, the BIGINT arithmetic is used for subtraction, addition, and multiplication. In this case, if you multiply two big integers (or integers returned by a function), an exception may occur if the multiplication result is greater than 9223372036854775807.

Fixed-point data types

The fixed-point data types are used to store precise numeric values with variable lengths. The value range and precision of a fixed-point data type depend on the precision and scale parameters and whether the data type is unsigned.

The DECIMAL data type is equivalent to the NUMERIC data type. Syntax:

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

M specifies the precision (total number of digits in the value that can be stored) and D specifies the scale (the number of digits after the decimal point). Decimal points (.) and minus signs (-) are not counted in M. If D is 0, the value does not have the decimal point or fractional part. The result of subtracting D from M is the maximum number of valid digits in the integer part.

In the DECIMAL syntax, the maximum value of M is 65, and the maximum value of D is 30. If D is not specified, the value of D is 0. If M is not specified, the value of M is 10. When neither M nor D is specified, the precision is 65 digits for the results of all basic operations (addition, subtraction, multiplication, and division) involving a DECIMAL column.

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.

For example, DECIMAL(5, 2) indicates that the maximum number of valid digits in the integral part is 3 and that in the fractional part is 2, so the value range is [-999.99, 999.99]. If the column is defined as UNSIGNED, the value range is [0, 999.99].

Data types in the following syntax are synonyms for the DECIMAL data type. Among them, FIXED is compatible with other database systems.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], 
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

Floating-point data types

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 53 and that of the scale is 30.

Notice

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 limits.

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
FLOAT	4	[-3.402823466E+38, -1.175494351E-38], 0, and [1.175494351E-38, 3.402823466E+38]	7 digits
DOUBLE	8	[-1.7976931348623157E+308, -2.2250738585072014E-308], 0, and [2.2250738585072014E-308, 1.7976931348623157E+308]	15 digits

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 data type is used to represent a small (single-precision) floating-point number. Syntax:

FLOAT [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 not specified, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

Note

The FLOAT(M,D) syntax is deprecated in the latest MySQL release. We recommend that you do not use this syntax.

For an OceanBase database, you can set D only to 0. The error message "Feature not supported" is reported if you set D to a non-zero value.

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. Syntax:

DOUBLE [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 not specified, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

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.

For an OceanBase database, you can set D only to 0. The error message "Feature not supported" is reported if you set D to a non-zero value.

DOUBLE PRECISION

The DOUBLE PRECISION data type is a synonym for the DOUBLE data type. Syntax:

DOUBLE PRECISION [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

Note

The DOUBLE PRECISION [(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.

BIT data type

The BIT data type is used to store bit values.

A bit value is specified by using the b'value' notation. valueis a binary value written in zeros and ones. For example, b'111' indicates 7, and b'10000000' indicates 128.

Syntax:

BIT[(M)]

M specifies the total number of digits in the value. The range of M is [1, 64]. If M is not specified, the value of M is 1.

When you insert a value into the BIT(M) column, if the length of the inserted value is less than the value of M, the value is left-padded with zeros. For example, if b'101' is inserted into BIT(6), it is equivalent to inserting b'000101'.