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 indicates the minimum display width.

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

Type	Length (bytes)	Value range (signed)	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 is unrelated to the range of values that can be stored. If you specify ZEROFILL for a numeric column, seekdb automatically adds the UNSIGNED attribute to the column.

BOOL/BOOLEAN

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

Here is an example:

SELECT IF(0, 'true', 'false');

The result is as follows:

+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+
1 row in set (0.001 sec)

SELECT IF(1, 'true', 'false');

The result is as follows:

+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+
1 row in set (0.000 sec)

SELECT IF(2, 'true', 'false');

The result is as follows:

+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+
1 row in set (0.000 sec)

SELECT IF(2 = FALSE, 'true', 'false');

The result is as follows:

+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+
1 row in set (0.001 sec)

SMALLINT

SMALLINT is used to represent a small integer. The syntax is as follows:

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

M indicates the maximum display width. The maximum display width is 255. The display width is unrelated to the range of values that can be stored. If you specify ZEROFILL for a numeric column, seekdb 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 indicates the maximum display width. The maximum display width is 255. The display width is unrelated to the range of values that can be stored. If you specify ZEROFILL for a numeric column, seekdb 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 indicates the maximum display width. The maximum display width is 255. The display width is unrelated to the range of values that can be stored. If you specify ZEROFILL for a numeric column, seekdb automatically adds the UNSIGNED attribute to the column.

In addition, seekdb supports extended types INT2 and INT4, but we recommend that you 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. The maximum display width is 255. The display width is unrelated to the range of values that can be stored. If you specify ZEROFILL for a numeric column, seekdb automatically adds the UNSIGNED attribute to the column.

When you use BIGINT, note the following:

• All operations must use signed BIGINT or DOUBLE values. Therefore, you cannot use unsigned large integers greater than 9223372036854775807 (63 bits), except for the BIT function. Otherwise, rounding errors may occur when you convert BIGINT values to DOUBLE, which may cause the last digit of the result to be incorrect.

• You can always store exact integer values in a BIGINT column by storing BIGINT values as strings. In this case, no intermediate conversion to double precision occurs when you convert strings to numbers.

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

SERIAL

The SERIAL data type is used to represent an auto-incrementing large integer (large range number) 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, meaning it can only be positive or zero.
• NOT NULL: The column value cannot be null, meaning every row must have a value.
• AUTO_INCREMENT: An auto-incrementing identifier, which automatically increases with the insertion of new rows.
• 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.

   CREATE TABLE tbl1 (id SERIAL, name VARCHAR(10));

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

   SHOW CREATE TABLE tbl1;

   The 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 (0.001 sec)