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 MySQL tenants, you can set the column type to AUTO_INCREMENT.
Auto-increment columns have three important attributes: starting value, step size, and cache size, which are determined by the following three tenant variable parameters.
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 (0.01 sec)
Example: Creating an auto-increment column
The following example creates an auto-increment column. You do not need to specify values for the auto-increment column when using the INSERT statement to insert records. OceanBase automatically adds values to this column.
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)
You can specify a value for the auto-increment column when using the INSERT statement. If the value is 0, OceanBase Database adds the next value of the sequence to the auto-increment column. If the value is smaller than the current maximum value, it does not affect the calculation of the next value in the auto-increment column. If the value is greater than the current maximum value, the auto-increment column uses the sum of the inserted value and cache value as the starting value for the next auto-increment.
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)