Integer data types |V3.2.4|OceanBase Database| docs|Distributed Database

Integer data types

Last Updated：2023-10-24 09:23:03 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.

Data type	Length (bytes)	Value range (signed)	Value range (unsigned)
`BOOL`, `BOOLEAN`, or `TINYINT`	1	[-2^7^, 2^7^ - 1]	[0, 2^8^ - 1]
`SMALLINT`	2	[-2^15^, 2^15^ - 1]	[0, 2^16^ - 1]
`MEDIUMINT`	3	[-2^23^, 2^23^ - 1]	[0, 2^24^ - 1]
`INT` or `INTEGER`	4	[-2^31^, 2^31^ - 1]	[0, 2^32^ - 1]
`BIGINT`	8	[-2^63^, 2^63^ - 1]	[0, 2^64^ - 1]

TINYINT

The TINYINT data type is used to represent a very small integer. Syntax:

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

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 column, OceanBase Database automatically adds the UNSIGNED attribute to the column.

BOOL or BOOLEAN

The BOOL or BOOLEAN data type is a synonym for the TINYINT data type. A zero value indicates false, whereas a non-zero value indicates true.

Examples:

obclient> SELECT IF(0, 'true', 'false');
+------------------------+
 IF(0, 'true', 'false') 
+------------------------+
 false                  
+------------------------+
1 row in set (0.01 sec)

obclient> SELECT IF(1, 'true', 'false');
+------------------------+
 IF(1, 'true', 'false') 
+------------------------+
 true                   
+------------------------+
1 row in set (0.00 sec)

obclient> SELECT IF(2, 'true', 'false');
+------------------------+
 IF(2, 'true', 'false') 
+------------------------+
 true                   
+------------------------+
1 row in set (0.00 sec)

obclient> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
 IF(2 = FALSE, 'true', 'false') 
+--------------------------------+
 false                          
+--------------------------------+
1 row in set (0.00 sec)

SMALLINT

The SMALLINT data type is used to represent a small integer. Syntax:

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

MEDIUMINT

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

Syntax:

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

INT or INTEGER

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

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

BIGINT

The BIGINT data type is used to represent a large integer. Syntax:

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

Usage notes for BIGINT:

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.