Numeric data types

OceanBase Database supports standard SQL numeric types, including exact numeric types, approximate numeric types, the BIT data type for storing bit values, and extended types.

Overview

Data type categories

The numeric types supported by the current version of OceanBase Database can be divided into the following four categories:

• Integer data types: BOOL/BOOLEAN/TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, and SERIAL

• Fixed-point types: DECIMAL and NUMERIC

• Floating-point types: FLOAT and DOUBLE

• Bit-value type: BIT

Numeric types can be specified with precision (the total number of digits in a number) and scale (the number of digits to the right of the decimal point) when they are defined. The meaning of precision and scale may vary for different numeric types. For more details, refer to the documentation of each type.

ZEROFILL attribute

You can use the ZEROFILL keyword to specify the minimum display width of a numeric data 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.

Here are some examples:

• INT(5) ZEROFILL indicates that when the data value is 123, it will be displayed as 00123.

• DECIMAL(10, 5) ZEROFILL indicates that when the data value is 123.456, it will be 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.

TINYINT

The TINYINT data type is used to represent a very small integer. The syntax is as follows:

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

Here, 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. Here is an 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. The syntax is as follows:

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

Here, 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.

MEDIUMINT

The MEDIUMINT data type is used to represent a medium-sized integer. The syntax is as follows:

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

Here, 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.

INT or INTEGER

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

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

Here, 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.

BIGINT

The BIGINT data type is used to represent a large integer. The syntax is as follows:

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

Here, 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.

When you use BIGINT, note that:

• 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 into 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.

SERIAL

The SERIAL data type is used to represent an auto-incrementing large integer (wide-range number) column. By default, the SERIAL data type is equivalent to creating a column of type BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Using the SERIAL data type makes it convenient to create columns with unique identifiers, making it especially suitable for use as a table's primary key.

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.

DECIMAL is equivalent to NUMERIC. The syntax is as follows:

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

Here, 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). The decimal point (.) and minus sign (-) 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 you specify the UNSIGNED attribute, 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 approximate numeric values with fixed lengths. The value range of a floating-point data type depends on the precision, scale, 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.

The following table describes the length and value range of each floating-point data type when the precision and scale are not specified.

If the precision and scale 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. The syntax is as follows:

FLOAT [UNSIGNED] [ZEROFILL]

Here, 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.

The following FLOAT syntax is also supported:

FLOAT(p) [UNSIGNED] [ZEROFILL]

Here, 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 [UNSIGNED] [ZEROFILL]

Here, 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.

DOUBLE PRECISION

The DOUBLE PRECISION data type is a synonym for the DOUBLE data type. The syntax is as follows:

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

Bit value data type

The BIT type is used to store bit values.

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

The syntax is as follows:

BIT[(M)]

Here, 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 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'.