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 TaurusDB are 1. To modify the parameters, perform the following steps:

1. On the Instances page, click the instance name.
2. In the navigation pane, choose Parameters. On the displayed page, modify parameters as required.

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 |