Define an auto-increment column

If you want the values of a numeric column to automatically increment when you create a table, you can define an auto-increment column. You can set the column type to AUTO_INCREMENT under a MySQL tenant.

Description

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

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 assigns values to it.

If you try to INSERT 0 to an auto-increment column, but SQL_MODE is not set to NO_AUTO_VALUE_ON_ZERO, OceanBase Database fills in the column with the next increment value of the auto-increment column. If this value is smaller than the current maximum value, this insertion does not affect the calculation of the next value in the column. If this value is larger than the current maximum value, OceanBase Database uses the sum of the inserted value and the cached value of the increment column as the start value of the next increment operation.

Note

If SQL_MODE is set to NO_AUTO_VALUE_ON_ZERO, the database does not fill in the auto-increment column with the next increment value when the value specified in the INSERT statement is 0.

Create a table named t1 and specify a column constraint

Example:

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

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

Insert a data record into the t1 table and set the value of the auto-increment column to 0

Example:

obclient> INSERT INTO t1(id, name) VALUES(0, 'D');
Query OK, 1 row affected

obclient> select * from t2;
+----+------+---------------------+
| 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.

Insert a data record into the t1 table and set the value of the auto-increment column to -1

Example:

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.

Insert a data record into the t1 table and set the value of the auto-increment column to 10

Example:

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

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 result:

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