About auto-increment columns

This topic describes how to use AUTO_INCREMENT in OceanBase Database in MySQL mode. You can use an auto-increment column to generate a unique identifier for a new record.

Background

If you want the values of a numeric column to automatically increment when you create a table, you can define an auto-increment column.

In MySQL mode of OceanBase, the column type can be defined as AUTO_INCREMENT, that is, an auto-increment column for a MySQL tenant.

Note

• AUTO_INCREMENT is an attribute of a data column that applies only to integer data columns.

• AUTO_INCREMENT data columns must be set with the NOT NULL attribute.

Prerequisites

The current user has the CREATE TABLE and ALTER TABLE permissions.

Attributes of an auto-increment column

Auto-increment columns have three important attributes: start value, increment step, and cache size, which are determined by the three tenant variables.

• View the start value, step size, and cache size of the auto-increment column of the current session.

  obclient> SHOW VARIABLES WHERE variable_name IN(
        'auto_increment_increment',
        'auto_increment_offset',
        'auto_increment_cache_size');
  +---------------------------+---------+
  | Variable_name             | Value   |
  +---------------------------+---------+
  | auto_increment_cache_size | 1000000 |
  | auto_increment_increment  | 1       |
  | auto_increment_offset     | 1       |
  +---------------------------+---------+
  3 rows in set
  

• Set the cache size of the auto-increment column to 1000, the start value to 10, and the step size to 2.

  obclient> SET GLOBAL auto_increment_cache_size=10000;
  Query OK, 0 rows affected
  
  obclient> SET auto_increment_offset=100;
  Query OK, 0 rows affected
  
  obclient> SET auto_increment_increment=2;
  Query OK, 0 rows affected
  

Mode of auto-increment columns

OceanBase Database allows you to specify the auto-increment mode for an auto-increment column.

To cope with different business scenarios and requirements, OceanBase Database supports the following two different auto-increment modes:

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

• NOORDER: This mode is implemented based on the distributed cache. The values are globally unique, offering better performance for partitioned tables.

Set the auto-increment mode

1. You can specify the default auto-increment mode for tables by using the parameter default_auto_increment_mode.

   If you do not explicitly specify the auto-increment mode when you create a table, the auto-increment mode specified for this parameter is used. The default value of this configuration item is ORDER.

   obclient> ALTER SYSTEM SET default_auto_increment_mode = 'ORDER|NOORDER';
   

2. You can specify the auto-increment mode of a table in the table option AUTO_INCRENENT_MODE.

    ... AUTO_INCRENENT_MODE= ORDER | NOORDER ...;
   

Examples of setting the auto-increment mode

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

  #Default mode
  obclient> CREATE TABLE tbl1 (col1 INT AUTO_INCREMENT);
  
  #Specify the ORDER mode.
  
  obclient> CREATE TABLE tbl2 (col1 INT AUTO_INCREMENT) AUTO_INCRENENT_MODE=ORDER;
  
  #Specify the NOORDER mode.
  obclient> CREATE TABLE tbl3 (col1 INT AUTO_INCREMENT) AUTO_INCRENENT_MODE=NOORDER;
  

• Modify the auto-increment mode of a table.

  obclient> ALTER TABLE tbl1 auto_increment_mode = 'ORDER';
  
  obclient> ALTER TABLE tbl1 auto_increment_mode = 'NOORDER';
  

Examples

In the following example, an auto-increment column is created. When you use the INSERT statement to insert records into a table that has an auto-increment column, 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. If this value is 0, 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. If this value is greater than the current maximum value, the auto-increment column uses the sum of the specified value and the cached value as the start value of the next increment.

1. 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 (0.01 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   
   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
   

   Note

   • Set the data type of the id column to bigint.
   • NOT NULL constraint: requires that the constrained column must not contain NULL values.
   • auto_increment: sets the column as an auto-increment column.
   • 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.
   • In the preceding example, the id column is set as an auto-increment column. When you use the INSERT statement to insert data records into a table that has an auto-increment column, you do not need to specify a value for the auto-increment column. OceanBase Database automatically assigns values to the column.

2. 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 |
   +----+------+---------------------+
   

   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.

3. 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
   

   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.

4. 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 (0.01 sec)
   
   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
   

   Because 10 is greater than the current maximum value of the auto-increment column, no next value is assigned to the auto-increment column. If you insert a data record G into the name column, the auto-increment column uses the sum of the specified value (10) and the cached value (1000000) as the start value (1000011) of the next increment.

   Query results:

   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 |
   | 1000011 | G    | 2020-04-03 17:10:34 |
   +---------+------+---------------------+
   7 rows in set