Integer types are fixed-length, exact numeric data types. Their value range depends on the type length and whether they are unsigned. Precision only indicates the minimum display width.
The following table describes the storage length and value range required for each integer type supported by OceanBase AI Database.
Type |
Length (bytes) |
Signed Value Range |
Value range (unsigned) |
|---|---|---|---|
BOOL/BOOLEAN/TINYINT |
1 | [-27, 27 - 1] | [0, 28 - 1] |
SMALLINT |
2 | [-215, 215 - 1] | [0, 216 - 1] |
MEDIUMINT |
3 | [-223, 223 - 1] | [0, 224 - 1] |
INT/INTEGER |
4 | [-231, 231 - 1] | [0, 232 - 1] |
BIGINT |
8 | [-263, 263 - 1] | [0, 264 - 1] |
SERIAL |
8 | N/A | [0, 264 - 1] |
TINYINT
TINYINT is used to represent a very small integer. The syntax is as follows:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
M indicates the maximum display width. The maximum display width is 255. The display width has nothing to do with the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to that column.
BOOL/BOOLEAN
The BOOL/BOOLEAN type is a synonym for TINYINT. A zero value indicates an error, and a non-zero value indicates correctness.
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
SMALLINT is used to represent a small integer value. The syntax is as follows:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width, which is 255. The display width has nothing to do with the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
MEDIUMINT
MEDIUMINT is used to represent a medium-sized integer.
The syntax is as follows:
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width, which is 255. The display width has nothing to do with the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
INT/INTEGER
INT or INTEGER is used to represent a normal-sized integer. The syntax is as follows:
INT[(M)] [UNSIGNED] [ZEROFILL]
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width, which is 255. The display width does not affect the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
In addition, OceanBase AI Database also supports the INT2 and INT4 data types. However, it is recommended to use INT instead of INT4.
BIGINT
BIGINT is used to represent a large integer. The syntax is as follows:
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
M indicates the maximum display width, which is 255. The display width has nothing to do with the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
The following points should be noted about BIGINT:
All operations require signed
BIGINTorDOUBLEvalues. Therefore, unsigned integers larger than 9223372036854775807 (63 bits) should not be used, except for theBITfunction. Otherwise, rounding errors may occur when converting aBIGINTvalue to aDOUBLE, potentially causing the last digit of the result to be incorrect.By storing
BIGINTvalues as strings, you can always ensure that precise integer values are stored in theBIGINTcolumns. In this case, converting strings to numbers does not involve intermediate double-precision conversions.When both operands are integer values, the - , + , and * operators perform
BIGINTarithmetic. If two large integers (or integer results returned by functions) are multiplied, an abnormal result may occur if the result exceeds 9223372036854775807.
SERIAL
The SERIAL data type is used to represent an auto-incrementing large integer (wide-range number) column. When using the SERIAL data type in a column definition, the following column attributes are effectively defined:
BIGINT: a large integer data type that can store very large numbers.UNSIGNED: Indicates that the integer is unsigned and can only be positive or zero.NOT NULL: The column value cannot be null, meaning each row must have a numeric value.AUTO_INCREMENT: an auto-increment identifier that automatically increments when a new row is 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 convenient to create columns with unique identifiers, making them particularly suitable for use as table primary keys.
Example:
Use the following SQL statement to create table
tbl1and define two columns: theidcolumn is defined as theSERIALdata type; thenamecolumn is defined as theVARCHAR(10)data type.obclient [test]> CREATE TABLE tbl1 (id SERIAL, name VARCHAR(10));Use the following SQL statement to view the definition of table
tbl1.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