About auto-increment columns

This topic describes how to use auto-increment columns in OceanBase Database in MySQL mode.

Prerequisites

The current user has the CREATE and ALTER privileges on the target table.

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 tenants, you can set the column type to AUTO_INCREMENT.

Attributes

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

Example

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

Parameters

Example

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 assign 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