Description
If you want the values of a numeric column to be automatically incremented rather than duplicated when creating a table, you need an auto-increment column. In a MySQL tenant, you can use the AUTO_INCREMENT clause to define an auto-increment column.
To define an auto-increment variable, you must specify three tenant variables that specify, respectively, the start value, the increment step, and the cache size of the auto-increment column.
| System variable | Description |
|---|---|
| auto_increment_cache_size | Specifies the cache size. Valid value range: [1, 100000000]. Default value: 1000000. |
| auto_increment_increment | Specifies the increment step. Valid value range: [1, 65535]. Default value: 1. |
| auto_increment_offset | Defines the start value of the AUTO_INCREMENT column. Valid value range: [1, 65535]. Default value: 1. |
Example
In the following example, an auto-increment column is created. When you use the INSERT statement to insert records to a table that has an auto-increment column, you do not need to specify a value for the auto-increment column. OceanBase Database automatically fills values in it.
If you try to INSERT 0 to an auto-increment column, 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.
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 (0.08 sec)
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 (0.01 sec)
obclient> INSERT INTO t1(id, name) VALUES(0, 'D');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO t1(id, name) VALUES(-1,'E');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO t1(id, name) VALUES(10,'F');
Query OK, 1 row affected (0.01 sec)
obclient> INSERT INTO t1(name) VALUES('G');
Query OK, 1 row affected (0.00 sec)
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 (0.00 sec)