Starting Value and Increment of AUTO_INCREMENT

The starting value and increment of AUTO_INCREMENT are determined by the auto_increment_offset and auto_increment_increment parameters.

• auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.
• auto_increment_increment controls the interval between successive column values.
• When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

• When the value of auto_increment_offset is less than or equal to that of auto_increment_increment, the value of AUTO_INCREMENT:

  auto_increment_offset + N x auto_increment_increment (N indicates the number of inserted data records).

The default values of the two parameters in GaussDB(for MySQL) are 1. To modify the parameters, perform the following steps:

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page, choose Database > GaussDB(for MySQL).
4. On the Instances page, click the instance name to go to the Basic Information page.
5. In the navigation pane on the left, choose Parameter Modification. On the displayed page, change parameters as needed.

For example:

1. If both auto_increment_offset and auto_increment_increment are set to 1, the starting value is 1 and the increment is 1.

   show variables like 'auto_inc%'; 
   +--------------------------+-------+ 
   | Variable_name            | Value | 
   +--------------------------+-------+ 
   | auto_increment_increment | 1     | 
   | auto_increment_offset    | 1     | 
   +--------------------------+-------+ 

2. If auto_increment_increment is set to 2, the increment is 2.

   set session auto_increment_offset=2; 
   Query OK, 0 rows affected (0.02 sec)  

   show variables like 'auto_inc%'; 
   +--------------------------+-------+ 
   | Variable_name            | Value | 
   +--------------------------+-------+ 
   | auto_increment_increment | 2     | 
   | auto_increment_offset    | 1     | 
   +--------------------------+-------+ 

3. If auto_increment_offset is set to 10 and auto_increment_increment is set to 2, the starting value is 2 (because the value of auto_increment_offset is greater than that of auto_increment_increment) and the increment is 2.

   set session auto_increment_offset=10; 
   set session auto_increment_increment=2; 
   show variables like 'auto_inc%'; 
   +--------------------------+-------+ 
   | Variable_name            | Value | 
   +--------------------------+-------+ 
   | auto_increment_increment | 2     | 
   | auto_increment_offset    | 10    | 
   +--------------------------+-------+  

   create table auto_test2(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)); 
   Query OK, 0 rows affected (0.08 sec)  

   show create table auto_test2; 
   CREATE TABLE `auto_test2` (   `id` int NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
   1 row in set (0.01 sec) 

   insert into auto_test2 values(0), (0), (0); 
   Query OK, 3 rows affected (0.00 sec) 
   Records: 3  Duplicates: 0  Warnings: 0  
   select * from auto_test2; 
   +----+ 
   | id | 
   +----+ 
   |  2 | 
   |  4 | 
   |  6 | 
   +----+ 
   3 rows in set (0.01 sec)  

4. If auto_increment_offset is set to 5 and auto_increment_increment is set to 10, the starting value is 5 and the increment is 10.

   set session auto_increment_offset=5; 
   set session auto_increment_increment=10; 
   show variables like 'auto_inc%'; 
   +--------------------------+-------+ 
   | Variable_name            | Value | 
   +--------------------------+-------+ 
   | auto_increment_increment | 10    | 
   | auto_increment_offset    | 5     | 
   +--------------------------+-------+ 

   create table auto_test3(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)); 
   insert into auto_test3 values(0), (0), (0); 
   Query OK, 3 rows affected (0.00 sec) 
   Records: 3  Duplicates: 0  Warnings: 0  
   select * from auto_test3; 
   +----+ 
   | id | 
   +----+ 
   |  5 | 
   | 15 | 
   | 25 |