If you need the values of a numeric column to be unique and increasing when you create a table, you can define an auto-increment column. You can set the column type to AUTO_INCREMENT, which is the auto-increment column for user tenants.
About auto-increment columns
Attributes of auto-increment columns
Auto-increment columns have three important attributes: the auto-increment start value, the auto-increment step, and the cache size of auto-increment values. These attributes are controlled by the following three tenant variables.
System Variables |
Description |
|---|---|
| auto_increment_cache_size | A global variable used to set the cache size of auto-increment values. Value range: [1, 100000000]. Default value: 1000000.
NoteOceanBase AI Database also supports the |
| auto_increment_increment | A session-level variable used to set the auto-increment step. Value range: [1, 65535]. Default value: 1. |
| auto_increment_offset | A session-level variable used to specify the start value of the AUTO_INCREMENT column. Value range: [1, 65535]. Default value: 1. |
For more information about system variables, see Overview of parameters and system variables.
Value hopping in auto-increment columns
In a MySQL database, an auto-increment column is a column attribute that automatically generates a unique, incremental value as the identifier for each row of data. As a distributed database, OceanBase AI Database typically spreads its tables across multiple servers. While striving for compatibility with MySQL databases, it also needs to ensure performance in generating auto-increment values across distributed multi-region scenarios, which can lead to value hopping during the generation process.
In OceanBase AI Database, auto-increment columns support two modes: NOORDER mode and ORDER mode. The default mode is ORDER mode. Specifically:
ORDER mode: An auto-increment column based on a centralized cache. When set to this mode, the values of the auto-increment column increase globally.
NOORDER mode: An auto-increment column based on a distributed cache. When set to this mode, only global uniqueness of the auto-increment values is guaranteed.
The following sections describe how value hopping occurs during generation in these two modes.
NOORDER mode
A table created in OceanBase AI Database by specifying AUTO_INCREMENT_MODE = 'NOORDER' is 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 node 1, OBServer node 2, and OBServer node 3 where a partitioned table is distributed receive an insert into values (null) request in order, the internal processing logic is as follows:
When OBServer node 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.When OBServer node 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.When OBServer node 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.OBServer node 1 generates an auto-increment value
2based on the cached value range [2,100].OBServer node 2 generates an auto-increment value
102based 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 are not smaller than this value.
In a distributed scenario of OceanBase AI 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 abandons the current value range when a maximum value is inserted. This way, no synchronization is required until the next cached value.
Assume that OBServer node 1, OBServer node 2, and OBServer node 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.
OBServer node 1 receives the value
1. Then, OBServer node 1 abandons the cached value range [1,100], requests a new value range [301,400] from the internal table, and synchronizes the value101to the internal table and other OBServer nodes.OBServer node 2 receives the value
2. OBServer node 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.OBServer node 3 receives the value
3. OBServer node 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.OBServer node 1 receives the value
4. OBServer node 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 occurs. For example, the first value range [1,100] of OBServer node 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:
Create a table named
t1that contains an auto-increment column.obclient> CREATE TABLE t1 (c1 int not null auto_increment) AUTO_INCREMENT_MODE='NOORDER';Set
auto_increment_cache_sizeto100.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);After the insertion is successful, 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
3to101.
Scenario 3: Server restart or failure
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 are not 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 node 1 is [1,100] and auto-increment values 1 and 2 have been generated. If OBServer node 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 AI Database introduces auto-increment columns in ORDER mode. ORDER mode 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 selects 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 requests an auto-increment value range from the internal table and caches 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 node 1, OBServer node 2, and OBServer node 3 where a partitioned table is distributed receive an insert into values (null) request in order, the internal processing logic is as follows:
OBServer node 1 finds that it is not the leader and therefore sends an RPC request to OBServer node 2. OBServer node 2 requests an auto-increment value range [1,100] from the internal table and returns an auto-increment value
1to OBServer node 1.OBServer node 2 finds that it is the leader and has cached a value range [2,100]. Therefore, OBServer node 2 directly generates an auto-increment value
2.OBServer node 3 finds that it is not the leader and therefore sends an RPC request to OBServer node 2. OBServer node 2 finds that it has cached a value range [3,100] and then returns an auto-increment value
3to OBServer node 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: Server restart or failure
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.
Notice
In this scenario, auto-increment value hopping occurs only when the OBServer node that serves as the leader is restarted or breaks down. Other OBServer nodes that serve as followers do not save value ranges. Therefore, the generation of continuous auto-increment values is not affected by the breakdown of followers.
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:
If OBServer node 1 becomes the new leader, OBServer node 1 requests a new auto-increment value range [101,200] from the internal table and generates auto-increment values
101and102.After OBServer node 2 is restarted, it becomes the leader again. OBServer node 2 continues to use the original cached value range [3,100] and generates auto-increment values
3and4.
Generated auto-increment values 101 and 3 are not incremental.
To avoid the preceding situation where auto-increment values are not incremental, OceanBase AI Database clears 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 AI 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 AI 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.
Note
If SQL_MODE is set to NO_AUTO_VALUE_ON_ZERO, it means that AUTO_INCREMENT should not be generated when INSERT into the column is zero.
Create a table named t1 and specify column constraints
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
idcolumn is set tobigint.NOT NULL: The constrained column cannot containNULLvalues.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 theidcolumn as the primary key column.name varchar(50): specifies that the data type of thenamecolumn isvarcharand the maximum character length of the column is50.Since the
idcolumn is set as an auto-increment column, when you use theINSERTstatement to insert data records into the table, you do not need to specify a value for the auto-increment column. OceanBase AI Database automatically assigns values to the column.
Insert a row into table t1 and specify the value of the auto-increment column as 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 AI Database automatically assigns the next value of the auto-increment column to the column to be inserted.
Insert a row into table t1 and specify the value of the auto-increment column as -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 row into table t1 and specify the value of the auto-increment column as 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 4 of the auto-increment column. If you insert a data record with the name column value G but no id column value into the t1 table, OceanBase AI Database automatically populates the next value of the auto-increment column, namely, the value following 10, in 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 regular column to an auto-increment column
OceanBase AI Database supports changing a regular column to an auto-increment column after the table is created.
The syntax is as follows:
obclient> ALTER TABLE table_name MODIFY column_name data_type AUTO_INCREMENT;
The parameters are described as follows:
table_name: The name of the table to be modified.column_name: The name of the column to be changed to an auto-increment column.data_type: The data type of the column to be modified.Note
When changing a column to an auto-increment column, you can also modify its data type, but ensure the new data type allows auto-increment.
For more information about data types, see Overview of data types.
Here is an example:
Create a table named
tbl1.obclient> CREATE TABLE tbl1(id bigint(10));Change a column of the
tbl1table to an auto-increment column.obclient> ALTER TABLE tbl1 MODIFY id bigint(10) AUTO_INCREMENT;
Set and modify the auto-increment mode of an auto-increment column
You can specify the auto-increment mode for an auto-increment column using the table-level parameter AUTO_INCREMENT_MODE when creating the table. You can also modify the auto-increment mode of an auto-increment column after the table is created. Additionally, you can use the tenant-level parameter default_auto_increment_mode to specify the default auto-increment mode for auto-increment columns in tables within a tenant.
Note
If you do not specify the auto-increment mode when you create a table, the system uses the auto-increment mode specified by the `default_auto_increment_mode` parameter for the table. The default value of the parameter is ORDER.
Here are some examples:
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 setCreate a table and then change 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 setSpecify 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
Modify the value of an auto-increment column
You can use the following syntax to set the next value of the auto-increment column in a table. The next value can be greater or smaller than the current value.
ALTER TABLE table_name AUTO_INCREMENT [=] int_value;
In the syntax, table_name specifies the table name and int_value specifies the next value of the auto-increment column.
Notice
When you decrease the value of the auto-increment column in a table, take note of the following considerations:
If the table already contains data and the maximum value of the auto-increment column is not smaller than the new value specified for AUTO_INCREMENT, the new value of AUTO_INCREMENT is automatically adjusted to the next value of the current maximum value of the auto-increment column. Assume that the current maximum value of the auto-increment column is 5, the current AUTO_INCREMENT value is 8, and the value range of AUTO_INCREMENT is [0,6]. After the statement is executed, the value of AUTO_INCREMENT is adjusted to 6.
Example
This example shows how to adjust the next value of an auto-increment column.
Increase the next value of an auto-increment column.
Create a table named
tbl1.obclient [test]> CREATE TABLE tbl1(id INT AUTO_INCREMENT, name VARCHAR(50));View the current value of the auto-increment column of
tbl1.obclient [test]> SHOW CREATE TABLE tbl1\GThe query result is as follows:
*************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL ) 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 setInsert five records into the
tbl1table.obclient [test]> INSERT INTO tbl1(name) VALUES('A1'),('A2'),('A3'),('A4'),('A5');The query result is as follows:
Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0View the current value of the auto-increment column of
tbl1again.obclient [test]> SHOW CREATE TABLE tbl1\GThe query result is as follows:
*************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL ) AUTO_INCREMENT = 6 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 setChange the next value of the auto-increment column of the
tbl1table to10.obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 10;View the current value of the auto-increment column of
tbl1again.obclient [test]> SHOW CREATE TABLE tbl1\GThe query result is as follows:
*************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL ) AUTO_INCREMENT = 10 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
Decrease the next value of an auto-increment column.
Change the next value of the auto-increment column of the
tbl1table to8.obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 8;View the current value of the auto-increment column of
tbl1.obclient [test]> SHOW CREATE TABLE tbl1\GThe query result is as follows:
*************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL ) AUTO_INCREMENT = 8 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 setDelete values smaller than or equal to
4from the auto-increment column of thetbl1table.obclient [test]> DELETE FROM tbl1 WHERE id <= 4;The query result is as follows:
Query OK, 4 rows affectedQuery the data in the
tbl1table.obclient [test]> SELECT * FROM tbl1;The query result is as follows:
+------+------+ | id | name | +------+------+ | 5 | A5 | +------+------+ 1 row in setExecute the following statement to change the next value of the auto-increment column of the
tbl1table to3:obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 3;The query result is as follows:
Query OK, 0 rows affectedView the current value of the auto-increment column of
tbl1again.obclient [test]> SHOW CREATE TABLE tbl1\GThe query result is as follows:
*************************** 1. row *************************** Table: tbl1 Create Table: CREATE TABLE `tbl1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL ) AUTO_INCREMENT = 6 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 setNote
The query result shows that the current value of
AUTO_INCREMENTis6. This is because the auto-increment column in the table already contains data and the current maximum value of the auto-increment column, which is5, is greater than the value3that you attempted to specify. Therefore, though the statement is successfully executed, the actual value ofAUTO_INCREMENTis automatically adjusted to the next value of the current maximum value, which is6.