Define an auto-increment column

If you want the values of a numeric column to automatically increment when you create a table, you can define an auto-increment column. You can set the column type to AUTO_INCREMENT under a MySQL tenant.

About auto-increment columns

Attributes of an auto-increment column

To define an auto-increment column, you must set three tenant variables that respectively specify the start value, the increment step, and the cache size of the auto-increment column.

For more information about system variables, see Parameters and system variables.

Value hopping of auto-increment columns

In MySQL Database, auto-increment columns are a column attribute in database tables. An auto-increment column can automatically generate unique incremental values to uniquely identify the data of the corresponding rows. OceanBase Database is a distributed database in which database tables are generally distributed across multiple different servers. OceanBase Database must ensure auto-increment column generation performance in distributed multi-server scenarios while ensuring compatibility with MySQL Database. This result in the issue of value hopping when auto-increment values are generated.

OceanBase Database supports two auto-increment modes for auto-increment columns: NOORDER and ORDER. The default mode is ORDER. The two auto-increment modes are described as follows:

• ORDER: This mode is implemented based on the centralized cache. In this mode, values in the auto-increment column increment globally.

• NOORDER: This mode is implemented based on the distributed cache. In this mode, values in the auto-increment column are globally unique.

The following sections describe how auto-increment values hop during generation in the two modes.

NOORDER mode

Tables created by specifying AUTO_INCREMENT_MODE = 'NOORDER' in OceanBase Database V4.x and all tables that contain auto-increment columns in OceanBase Database of a version earlier than V4.0.0 are in NOORDER mode.

The following figure shows the working principle of an auto-increment column in NOORDER mode.

As shown in the preceding figure, the data structure of an auto-increment column in NOORDER mode consists of two parts:

• Internal table: persists the checkpoint of auto-increment values that have been used.

• Cache: records, in the internal structure, an auto-increment value range that is requested from the internal table.

For an auto-increment column in NOORDER mode, OBServer nodes are independent of each other. Each OBServer node can obtain an auto-increment value range from the internal table and record the range in the server cache to accelerate the generation of auto-increment values. The following typical scenarios demonstrate why auto-increment values hop in NOORDER mode.

Scenario 1: Auto-increment values are generated for multiple partitions on multiple servers

Assume that auto_increment_cache_size is set to 100. When OBServer 1, OBServer 2, and OBServer 3 where a partitioned table is distributed receive an insert into values (null) request in order, the internal processing logic is as follows:

1. When OBServer 1 finds that it has no value range cached, it requests an auto-increment value range [1,100] from the internal table and generates an auto-increment value 1.

2. When OBServer 2 finds that it has no value range cached, it requests an auto-increment value range [101,200] from the internal table and generates an auto-increment value 101.

3. When OBServer 3 finds that it has no value range cached, it requests an auto-increment value range [201,300] from the internal table and generates an auto-increment value 201.

4. OBServer 1 generates auto-increment value 2 based on the cached value range [2,100].

5. OBServer 2 generates auto-increment value 102 based on the cached value range [102,200].

   …

This way, values 1, 101, 201, 2, 102, ... are inserted into the table in order, resulting in value hopping.

Scenario 2: The INSERT statement is used to insert a specified maximum value

In MySQL Database, if a specified value is explicitly inserted into the auto-increment column of a table, the auto-increment values generated later will not be smaller than this value.

In a distributed scenario of OceanBase Database, when an OBServer node learns that a maximum value is inserted, it must synchronize the information to other OBServer nodes and the internal table. The synchronization operation is time-consuming. To avoid synchronization each time when a specified maximum value is inserted, the system will abandon the current value range when a maximum value is inserted. This way, no synchronization is required until the next cached value.

Assume that OBServer 1, OBServer 2, and OBServer 3 where a partitioned table is distributed receive a request to explicitly specify an incremental sequence (1, 2, 3, ...) in order, and each of the servers caches a value range.

1. OBServer 1 receives the value 1. Then, OBServer 1 abandons the cached value range [1,100], requests a new value range [301,400] from the internal table, and synchronizes the value 101 to the internal table and other OBServer nodes.

2. OBServer 2 receives the value 2. OBServer 2 compares the received value with the cached value range [101,200] and does not take any action because the received value is smaller than the smallest value of the value range.

3. OBServer 3 receives the value 3. OBServer 3 compares the received value with the cached value range [201,300] and does not take any action because the received value is smaller than the smallest value of the value range.

4. OBServer 1 receives the value 4. OBServer 1 compares the received value with the cached value range [301,400] and does not take any action because the received value is smaller than the smallest value of the value range. ...

This way, if the auto-increment column is still used to generate a sequence after some values are inserted, hopping of auto-increment values will occur. For example, the first value range [1,100] of OBServer 1 is not used. The start value of the new value range directly hops to 301.

Besides a multi-server environment, hopping of auto-increment values also occurs in a single-server environment when a specified maximum value is inserted. Here is an example:

1. Create a table named t1 that contains an auto-increment column.

   obclient> CREATE TABLE t1 (c1 int not null auto_increment) AUTO_INCREMENT_MODE='NOORDER';
   

   Set auto_increment_cache_size to 100.

2. Insert data into the table multiple times.

   obclient> INSERT INTO t1 VALUES(null);
   

   obclient> INSERT INTO t1 VALUES(3);
   

   obclient> INSERT INTO t1 VALUES(null);
   

3. View the data in the table.

   obclient> SELECT * FROM t1;
   

   The query result is as follows:

   +-----+
   | c1  |
   +-----+
   |   1 |
   |   3 |
   | 101 |
   +-----+
   

   The query result shows that the value in the auto-increment column hops from 3 to 101.

Scenario 3: A server is restarted or breaks down

The cache of an auto-increment column is a memory structure. If an OBServer node is restarted or breaks down, the unused values in the value range cached on the OBServer node will not be written back to the internal table and will no longer be used. For example, assume that the initial auto-increment value range cached on OBServer 1 is [1,100] and auto-increment values 1 and 2 have been generated. If OBServer 1 breaks down and is restarted, the value range cached on this server changes to [101,200] and the next auto-increment value is 101. As a result, the auto-increment values are 1, 2, 101, ..., resulting in value hopping.

ORDER mode

To avoid the issue of auto-increment value hopping in NOORDER mode in common scenarios where auto-increment values are generated for multiple partitions on multiple servers or the INSERT statement is used to insert a specified maximum value, OceanBase Database supports auto-increment columns in ORDER mode since V4.x. ORDER is the default mode for tables and has better compatibility with MySQL Database.

The following figure shows the working principle of an auto-increment column in ORDER mode.

In ORDER mode, an auto-increment column will select the OBServer node which is the leader of the current cluster to provide services. Other OBServer nodes that serve as followers must send a remote procedure call (RPC) request to apply for auto-increment values from the leader. The leader will request an auto-increment value range from the internal table and cache the value range.

For example, assume that auto_increment_cache_size is set to 100 in a scenario where auto-increment values are generated for multiple partitions on multiple servers. When OBServer 1, OBServer 2, OBServer 3 where a partitioned table is distributed receive an insert into values (null) request in order, the internal processing logic is as follows:

1. OBServer 1 finds that it is not the leader and therefore sends an RPC request to OBServer 2. OBServer 2 requests an auto-increment value range [1,100] from the internal table and returns an auto-increment value 1 to OBServer 1.

2. OBServer 2 finds that it is the leader and has cached a value range [2,100]. Therefore, OBServer 2 directly generates an auto-increment value 2.

3. OBServer 3 finds that it is not the leader and therefore sends an RPC request to OBServer 2. OBServer 2 finds that it has cached a value range [3,100] and then returns an auto-increment value 3 to OBServer 3.

   …

In ORDER mode, all OBServer nodes that serve as followers request auto-increment values from the leader, and the system can always generate continuous auto-increment values, which is the same as a single-server scenario. However, the ORDER mode has poorer performance than the NOORDER mode in a high-concurrency multi-server scenario.

For auto-increment columns in ORDER mode, the issue of auto-increment value hopping is resolved in scenarios where auto-increment columns are generated for multiple partitions on multiple servers or the INSERT statement is used to insert a specified maximum value. However, the issue still occurs in scenarios where the OBServer node that serves as the leader is restarted or breaks down or leader switching occurs.

Scenario 1: The leader is restarted or breaks down

In ORDER mode, the OBServer node that serves as the leader saves the value ranges cached in the memory. When the OBServer node that serves as the leader is restarted or breaks down, the unused auto-increment values in the current value range will no longer be used, and a new value range is used. As a result, auto-increment value hopping occurs.

Scenario 2: Leader switching occurs

Assume that the initial auto-increment value range cached on OBServer 2 is [1,100] and auto-increment values 1 and 2 have been generated. When leader switching occurs in the cluster, the general processing logic is as follows:

1. If OBServer 1 becomes the new leader, OBServer 1 requests a new auto-increment value range [101,200] from the internal table and generates auto-increment values 101 and 102.

2. After OBServer 2 is restarted, it becomes the leader again. OBServer 2 continues to use the original cached value range [3,100] and generates auto-increment values 3 and 4.

Generated auto-increment values 101 and 3 are not incremental.

To avoid the preceding situation where auto-increment values are not incremental, OceanBase Database will clear the value range cached on the original leader when leader switching occurs. This results in auto-increment value hopping.

Create an auto-increment column

If you specified an auto-increment column when you created a table, when you use the INSERT statement to insert records into the table, you do not need to specify a value for the auto-increment column. OceanBase Database automatically assigns values to it.

Assume that a value of the auto-increment column is specified in the INSERT statement and SQL_MODE is not set to NO_AUTO_VALUE_ON_ZERO. If this value is 0 or is greater than the current maximum value, OceanBase Database automatically assigns the next value of the auto-increment column to the column to be inserted. If this value is smaller than the current maximum value, the calculation of the next value of the auto-increment column is not affected.

Create a table named t1 and specify a column constraint

obclient> CREATE TABLE t1(id bigint not null auto_increment primary key, name varchar(50), gmt_create timestamp not null default current_timestamp);
Query OK, 0 rows affected

obclient> INSERT INTO t1(name) VALUES('A'),('B'),('C');
Query OK, 3 rows affected

obclient> SELECT * FROM t1;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
|  1 | A    | 2020-04-03 17:09:55 |
|  2 | B    | 2020-04-03 17:09:55 |
|  3 | C    | 2020-04-03 17:09:55 |
+----+------+---------------------+
3 rows in set

This example involves the following settings:

• The data type of the id column is set to bigint.

• NOT NULL constraint: requires that the constrained column do not contain NULL values.

• auto_increment: sets the column as an auto-increment column. You can set only non-BOOL/BOOLEAN integer columns as auto-increment columns.

• primary key: sets the id column as the primary key column.

• name varchar(50): specifies that the data type of the name column is varchar and the maximum character length of the column is 50.

• Since the id column is set as an auto-increment column, when you use the INSERT statement to insert data records into the table, you do not need to specify a value for the auto-increment column. OceanBase Database automatically assigns values to the column.

Insert a data record into the t1 table and set the value of the auto-increment column to 0

obclient> INSERT INTO t1(id, name) VALUES(0, 'D');
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
|  1 | A    | 2021-12-17 14:21:53 |
|  2 | B    | 2021-12-17 14:21:53 |
|  3 | C    | 2021-12-17 14:21:53 |
|  4 | D    | 2021-12-17 14:22:36 |
+----+------+---------------------+

In the example, the value 0 is specified for the auto-increment column in the INSERT statement. In this case, OceanBase Database automatically assigns the next value of the auto-increment column to the column to be inserted.

Insert a data record into the t1 table and set the value of the auto-increment column to -1

obclient> INSERT INTO t1(id, name) VALUES(-1,'E');
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
| -1 | E    | 2021-12-17 15:25:04 |
|  1 | A    | 2021-12-17 14:21:53 |
|  2 | B    | 2021-12-17 14:21:53 |
|  3 | C    | 2021-12-17 14:21:53 |
|  4 | D    | 2021-12-17 14:22:36 |
+----+------+---------------------+
5 rows in set

In the example, because -1 is smaller than the current maximum value of the auto-increment column, the calculation of the next value of the auto-increment column is not affected.

Insert a data record into the t1 table and set the value of the auto-increment column to 10

obclient> INSERT INTO t1(id, name) VALUES(10,'F');
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
| -1 | E    | 2021-12-17 15:25:04 |
|  1 | A    | 2021-12-17 14:21:53 |
|  2 | B    | 2021-12-17 14:21:53 |
|  3 | C    | 2021-12-17 14:21:53 |
|  4 | D    | 2021-12-17 14:22:36 |
| 10 | F    | 2021-12-17 15:33:28 |
+----+------+---------------------+
6 rows in set

10 is greater than the current maximum value of the auto-increment column, and is therefore directly used as the value of the id column. If you insert a data record G into the name column, OceanBase Database automatically assigns the value of the auto-increment column, namely the id column.

The query result is as follows:

obclient> INSERT INTO t1(name) VALUES('G');
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+---------+------+---------------------+
| id      | name | gmt_create          |
+---------+------+---------------------+
|      -1 | E    | 2020-04-03 17:10:24 |
|       1 | A    | 2020-04-03 17:09:55 |
|       2 | B    | 2020-04-03 17:09:55 |
|       3 | C    | 2020-04-03 17:09:55 |
|       4 | D    | 2020-04-03 17:10:19 |
|      10 | F    | 2020-04-03 17:10:29 |
|      11 | G    | 2020-04-03 17:10:34 |
+---------+------+---------------------+
7 rows in set

Change a normal column to an auto-increment column

After you create a table, you can change a normal column in the table to an auto-increment column.

The syntax is as follows:

obclient> ALTER TABLE table_name MODIFY column_name data_type AUTO_INCREMENT;

where

• table_name specifies the name of the table to be modified.

• column_name specifies the name of the column to be changed to an auto-increment column.

• data_type specifies the data type of the column to be modified.

  Note

  When you change a column to an auto-increment column, you can modify the data type of the column. Make sure that the new data type supports auto increment.

  For more information about data types, see Data types.

Here is an example:

1. Create a table named tbl1.

   obclient> CREATE TABLE tbl1(id bigint(10));
   

2. Change a column of the tbl1 table to an auto-increment column.

   obclient> ALTER TABLE tbl1 MODIFY id bigint(10) AUTO_INCREMENT;
   

Specify and modify the mode of an auto-increment column

When you create a table, you can use the AUTO_INCRENENT_MODE parameter to specify the auto-increment mode of an auto-increment column in the table. You can also modify the mode after you create the table. Alternatively, you can use the default_auto_increment_mode parameter to specify a default auto-increment mode for auto-increment columns in tables of a specific tenant.

Here is an example:

• Specify the auto-increment mode when you create a table.

  obclient> CREATE TABLE tbl2(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50)) AUTO_INCREMENT_MODE='NOORDER';
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'NOORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  

• After you create a table, modify the auto-increment mode of an auto-increment column in the table.

  obclient> CREATE TABLE tbl2(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  
  obclient> ALTER TABLE tbl2 auto_increment_mode = 'NOORDER';
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'NOORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  

• Specify a default auto-increment mode for auto-increment columns in tables of a specific tenant.

  obclient> ALTER SYSTEM SET default_auto_increment_mode = 'ORDER';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl3\G
  *************************** 1. row ***************************
        Table: tbl3
  Create Table: CREATE TABLE `tbl3` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set