Integer data types|V4.3.3| docs|Distributed Database

Integer data types

Last Updated：2025-11-27 02:38:06 Updated

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 (byte)	Value range (signed)	Value range (unsigned)
`BOOL`/`BOOLEAN`/`TINYINT`	1	[-2⁷, 2⁷ - 1]	[0, 2⁸ - 1]
`SMALLINT`	2	[-2¹⁵, 2¹⁵ - 1]	[0, 2¹⁶ - 1]
`MEDIUMINT`	3	[-2²³, 2²³ - 1]	[0, 2²⁴ - 1]
`INT`/`INTEGER`	4	[-2³¹, 2³¹ - 1]	[0, 2³² - 1]
`BIGINT`	8	[-2⁶³, 2⁶³ - 1]	[0, 2⁶⁴ - 1]
`SERIAL`	8	N/A	[0, 2⁶⁴ - 1]

TINYINT

The TINYINT data type is used to represent very small integers. 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 small integers. The syntax is as follows:

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

MEDIUMINT

The MEDIUMINT data type is used to represent medium-sized integers.

The syntax is as follows:

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

INT/INTEGER

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

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

In addition, OceanBase Database supports the extended INT2 and INT4 types, but we recommend that you use INT instead of INT4.

BIGINT

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

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

Take note of the following considerations:

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.

SERIAL

The SERIAL data type is used to represent an auto-increment large integer column. If you use the SERIAL data type in the definition of a column, the following column attributes are actually defined:

BIGINT: indicates a large integer type, which can store very large numbers.
UNSIGNED: indicates an unsigned integer, which can only be 0 or a positive integer.
NOT NULL: indicates that the column values cannot be empty. In other words, the column must have a value in each row.
AUTO_INCREMENT: indicates that the column is an auto-increment column. When you insert a new row, the value of the column automatically increments.
UNIQUE: indicates that all values in the column are unique.

Specifically, using the SERIAL data type is equivalent to creating a column of the BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE type. You can use the SERIAL data type to conveniently create a column with a unique identifier. This column is particularly suitable for being used as a primary key of the table.

Here is an example:

Create a table named tbl1 with two columns. The id column is of the SERIAL data type, and the name column is of the VARCHAR(10) data type.
```
obclient [test]> CREATE TABLE tbl1 (id SERIAL, name VARCHAR(10));
```

Query the definition of the tbl1 table.

obclient [test]> SHOW CREATE TABLE tbl1;

The return result is as follows:

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1  | CREATE TABLE `tbl1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `id` (`id`) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set