定义自增列

如果创建表时需要某个数值列的值不重复并且保持递增，这就是自增列。列的类型可以定义为 AUTO_INCREMENT，即 MySQL 租户的自增列。

描述

自增列有三个重要属性，包括自增起始值、自增步长和自增列缓存大小，通过以下三个租户变量参数来控制。

系统变量的设置方法及说明请参见 系统变量概述。

创建自增列

如果在创建表时指定了自增列，则在使用 INSERT 语句插入记录时不需要指定自增列的值，OceanBase 数据库会自动为该列填充值。 如果您在 INSERT 时指定了自增列的值，且 SQL_MODE 的值未设置为 NO_AUTO_VALUE_ON_ZERO ，当指定的值为 0 或比当前值的最大值还大时，OceanBase 数据库会用自增列的下一个值填充列的值；当指定的值比当前最大值小时，不影响自增列的下一个值的计算。

说明

如果 SQL_MODE 设置为 NO_AUTO_VALUE_ON_ZERO ，则表示不要在列中的 INSERT 为零时生成 AUTO_INCREMENT。

创建 t1 表并指定列约束条件

示例如下：

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

示例中：

• 指定 id 列的数据类型为 bigint。

• 非空约束（NOT NULL）：不允许约束包含的列的值为 NULL。

• auto_increment：设置自增列。当前仅数据类型为整数类型的数据列（BOOL/BOOLEAN 类型除外）可以设置为自增列。

• primary key：设置 id 列为主键列。

• name varchar(50)：允许 name 列插入的数据类型为字符型（varchar），50 表示最大字符长度。

• 由于设置了 id 列为自增列，所以在使用 INSERT 语句插入记录时不需要指定自增列的值，OceanBase 数据库会自动为该列填充值。

在 t1 表中插入一条数据且指定自增列的值为 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 |
+----+------+---------------------+

根据示例可知，在 INSERT 时指定了自增列的值，且这个值为 0，则 OceanBase 数据库会用自增列的下一个值填充列的值。

在 t1 表中插入一条数据并指定自增列的值为 -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

根据示例结果，由于 -1 比当前自增列的最大值小，所以不影响自增列的下一个值的计算。

在 t1 表中插入一条数据并指定自增列的值为 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

由于 10 比当前自增列的最大值大，所以此时不指定自增列的值，在 name 列插入一条数据 G , OceanBase 数据库会用自增列的下一个值填充列的值。

查询结果如下：

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

将普通列修改为自增列

OceanBase 数据库支持在创建表后，将表中的普通列修改为自增列。

语法如下：

obclient> ALTER TABLE table_name MODIFY column_name data_type AUTO_INCREMENT;

语法使用说明：

• table_name：待修改的表名。

• column_name：待修改为自增列的列名。

• data_type：待修改的列的数据类型。

  说明

  修改为自增列时，可以同时修改该列的数据类型，但需要确保修改后的数据类型是允许自增的数据类型。

  关于数据类型的更多介绍，请参见 数据类型概述。

示例：

1. 创建一个表 tbl1。

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

2. 将表 tbl1 的列修改为自增列。

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

设置和修改自增列的模式

OceanBase 数据库支持为自增列设置自增模式：

• ORDER 模式：基于集中缓存的自增列。设置为该模式后，自增列的值全局递增。

• NOORDER 模式：基于分布式缓存的自增列，设置为该模式后，仅保证自增列的值全局唯一。

您可以在创建表时通过表级参数 AUTO_INCRENENT_MODE 为自增列指定自增模式，也可以待表创建成功后，再修改表中自增列的自增模式；或者还可以通过租户级配置项 default_auto_increment_mode 来指定租户内表的自增列的默认自增模式。

说明

如果在建表时未指定自增模式，则表创建成功后，系统会按照配置项的自增模式来指定，该配置项默认为 ORDER。

示例如下：

• 创建表时指定自增模式。

  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
  

• 创建表后修改表中自增列的自增模式。

  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
  

• 通过配置项指定租户内表的自增列的默认自增模式。

  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 = '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