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 are some examples:

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/INTEGER

The INT/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.

In addition, OceanBase Database supports the extended INT2 and INT4 types, but we recommend that you replace INT4 with INT.

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.

Considerations for BIGINT are as follows:

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