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]
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.
MEDIUMINT
The MEDIUMINT data type is used to represent a medium-sized integer.
Syntax:
MEDIUMINT[(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.
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]
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.
BIGINT
The BIGINT data type is used to represent a large integer. Syntax:
BIGINT[(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.
Usage notes for BIGINT:
You must use signed
BIGINTorDOUBLEvalues in all operations. Therefore, you cannot use unsigned integers that are larger than 9223372036854775807 in value or greater than 63 bits in length except inBITfunctions. Otherwise, a rounding error may occur when you convert aBIGINTvalue into aDOUBLEvalue. As a result, the last digit in the conversion result may be incorrect.You can store an exact integer in a
BIGINTcolumn 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
BIGINTarithmetic 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.