Integer types

Integer types are fixed-length, exact numeric types. The value range depends on the type length and whether it is unsigned. The precision only indicates the minimum display width.

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

TINYINT

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

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

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

BOOL/BOOLEAN

The BOOL/BOOLEAN type is a synonym for TINYINT. A zero value indicates an error, and a non-zero value indicates a correct value.

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 type is used to store a small integer. The syntax is as follows:

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

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

MEDIUMINT

The MEDIUMINT type is used to store a medium-sized integer.

The syntax is as follows:

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

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

INT/INTEGER

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

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

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

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

BIGINT

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

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

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

When you use the BIGINT type, note the following:

• You must use signed BIGINT or DOUBLE values for all operations. Therefore, do not use unsigned large integers greater than 9223372036854775807 (63 bits), except for the BIT function. Otherwise, rounding errors will occur when you convert a BIGINT value to a DOUBLE value, and the last digit of the result may be incorrect.

• You can always store exact integer values in a BIGINT column by using strings. In this case, no intermediate conversion to double precision is involved when you convert a string to a number.

• When both operands are integers, the - and + operators and the * operator use BIGINT operations. If you multiply two large integers (or the integer results returned by functions), the result may be incorrect if it exceeds 9223372036854775807.

SERIAL

The SERIAL data type is used to store an automatically incremented large integer (a large range of numbers) column. When you use the SERIAL data type in a column definition, the following column attributes are defined:

• BIGINT: a large integer data type that can store very large numbers.
• UNSIGNED: indicates that the integer is unsigned and can only be a positive number or zero.
• NOT NULL: the column value cannot be null; that is, each row must have a numeric value.
• AUTO_INCREMENT: an auto-increment identifier. The value of this column automatically increases with each new row inserted.
• UNIQUE: ensures that all values in the column are unique.

Specifically, the SERIAL data type is equivalent to creating a BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE column by default. Using the SERIAL data type makes it easy to create columns with unique identifiers, especially suitable for use as the primary key of a table.

Here is an example:

1. Use the following SQL statement to create a table named tbl1 with two fields: the id field is defined as the SERIAL data type, and the name field is defined as the VARCHAR(10) data type.

   obclient [test]> CREATE TABLE tbl1 (id SERIAL, name VARCHAR(10));
   

2. Use the following SQL statement to view the definition of the tbl1 table.

   obclient [test]> SHOW CREATE TABLE tbl1;
   

   The returned 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