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

To define an auto-increment column, you must set three tenant variables that respectively specify the start value, the increment step, and the cache size of the auto-increment column.

For more information about system variables, see Overview of system variables.

Create an auto-increment column

If you specified an auto-increment column when you created a table, when you use the INSERT statement to insert records into the table, 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 and SQL_MODE is not set to NO_AUTO_VALUE_ON_ZERO. If this value is 0 or is greater than the current maximum value, 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.

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

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

In the example:

• The data type of the id column is set 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. You can set only non-BOOL/BOOLEAN integer columns as auto-increment columns.

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

• Since the id column is set as an auto-increment column, when you use the INSERT statement to insert data records into the table, 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

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

In the example, 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

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

In the example, 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

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, OceanBase Database automatically assigns the next value of the auto-increment column to the column to be inserted.

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

Change a normal column to an auto-increment column

After you create a table, you can change a normal column in the table to an auto-increment column.

Syntax:

obclient> ALTER TABLE table_name MODIFY column_name data_type AUTO_INCREMENT;

Parameters:

• table_name: the name of the table to be modified.

• column_name: the name of the column to be changed to an auto-increment column.

• data_type: the data type of the column to be modified.

For more information about data types, see Overview.

Example:

1. Create a table named tbl1.

   obclient> CREATE TABLE tbl1(id bigint(10));
   

2. Change a column of the tbl1 table to an auto-increment column.

   obclient> ALTER TABLE tbl1 MODIFY id bigint(10) AUTO_INCREMENT;
   

Specify and modify the mode of an auto-increment column

You can specify the auto-increment mode of an auto-increment column.

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

• NOORDER: This mode is implemented based on the distributed cache. In this mode, values in the auto-increment column are globally unique.

When you create a table, you can use the AUTO_INCRENENT_MODE parameter to specify the auto-increment mode of an auto-increment column in the table. You can also modify the mode after you create the table. Alternatively, you can use the default_auto_increment_mode parameter to specify a default auto-increment mode for auto-increment columns in tables of a specific tenant.

Examples:

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

  obclient> CREATE TABLE tbl2(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50)) AUTO_INCREMENT_MODE='NOORDER';
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'NOORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  

• After you create a table, modify the auto-increment mode of an auto-increment column in the table.

  obclient> CREATE TABLE tbl2(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  
  obclient> ALTER TABLE tbl2 auto_increment_mode = 'NOORDER';
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl2\G
  *************************** 1. row ***************************
        Table: tbl2
  Create Table: CREATE TABLE `tbl2` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'NOORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set
  

• Specify a default auto-increment mode for auto-increment columns in tables of a specific tenant.

  obclient> ALTER SYSTEM SET default_auto_increment_mode = 'ORDER';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(id bigint(10) AUTO_INCREMENT PRIMARY KEY, name varchar(50));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl3\G
  *************************** 1. row ***************************
        Table: tbl3
  Create Table: CREATE TABLE `tbl3` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'NOORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
  1 row in set