定义自增列

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

关于自增列

自增列的属性

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

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

自增列的跳变

在 MySQL 数据库中，自增列是数据库表中的一种列属性，它可以自动生成一个唯一的、递增的值，用于表示该行数据的唯一标识。

在 OceanBase 数据库中，自增列支持两种自增模式，即 NOORDER 模式和 ORDER 模式，默认为 ORDER 模式。其中：

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

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

下面从这两个模式来分别介绍自增值在生成过程中是如何发生跳变的。

NOORDER 模式

OceanBase 数据库 V4.x 版本中通过指定 AUTO_INCREMENT_MODE = 'NOORDER' 创建的表，以及 V4.0.0（不含该版本）以下版本中创建的所有含自增列的表，均为 NOORDER 模式的自增表。

对于 NOORDER 模式的自增列，其内部原理如下图所示。

从上图可知，NOORDER 模式的自增列，其数据结构分为以下两部分：

• 内部表：负责持久化当前已经使用的自增值位点。

• 缓存：记录在内部结构中的一段自增值区间，通过向内部表申请获得。

NOORDER 模式的自增列中的每一个 OBServer 节点之间均保持独立，各节点可以自主从内部表获取自增区间记录到机器缓存中，从而加速自增值的生成。下面以几个典型场景为例，介绍 NOORDER 模式下自增值出现跳变的原因。

场景 1：多机多分区生成自增值

假设 auto_increment_cache_size 的值为 100，当分区表所在的 OBServer 节点 OBServer1、OBServer2 和 OBServer3 分别按以下顺序先后接收到 insert into values (null) 的请求时，它们内部的处理逻辑如下：

1. OBServer1 发现自身没有缓存时，向内部表申请一段自增区间 [1,100]，并且生成一个自增值 1。

2. OBServer2 发现自身没有缓存时，向内部表申请一段自增区间 [101,200]，并且生成一个自增值 101。

3. OBServer3 发现自身没有缓存时，向内部表申请一段自增区间 [201,300]，并且生成一个自增值 201。

4. OBServer1 使用缓存 [2,100] 生成自增值 2。

5. OBServer2 使用缓存 [102,200] 生成自增值 102。

……

这样，表内插入数据的顺序就是 1，101，201，2，102，...，可以发现，自增值总是在发生跳变。

场景 2：通过 INSERT 语句插入指定的最大值

在 MySQL 数据库中，如果显式地向自增表中插入指定值，则后续生成的自增值都不会小于该值。

在 OceanBase 数据库的分布式场景下，当插入一个指定值且该值比自增表中其他值都大（即最大值）时，不仅 OBServer 节点自身需要知道当前插入了一个最大值，还需要同步给其它 OBServer 节点和内部表，该同步动作非常耗时，为了避免每次指定最大值时都执行同步操作，系统会在插入一个最大值时放弃当前的缓存，这样从当前值开始到下一个缓存值前都不需要再进行同步。

例如，当分区表所在的 OBServer1、OBServer2、OBServer3 分别按以下顺序先后接收到显式指定递增序列（1, 2, 3, ...）的请求时，并且假设这些机器上均保存了缓存：

1. OBServer1 接收到值 1，放弃缓存 [1,100]，重新从内部表获取到一段新的缓存区间 [301,400]，并且把 101 作为一个同步值同步到内部表和其它 OBServer 节点。

2. OBServer2 接收到值 2，对比发现该值比当前的缓存区间 [101,200] 中的值小，不做操作。

3. OBServer3 接收到值 3，对比发现该值比当前的缓存区间 [201,300] 中的值小，不做操作。

4. OBServer1 接收到值 4，对比发现该值比当前的缓存区间 [301,400] 中的值小，不做操作。 ...

这样，如果插入部分值后，继续使用自增列来生成序列，就会发生自增值跳变。例如，OBServer1 的第一个区间 [1,100] 都没有使用而是直接跳到了 301。

除了多机环境，单机环境下，插入指定的最大值时，也会出现自增值跳变的问题。示例如下：

1. 创建一个含自增列的表 t1。

   obclient> CREATE TABLE t1 (c1 int not null auto_increment) AUTO_INCREMENT_MODE='NOORDER';
   

   同时，auto_increment_cache_size 的值为 100。

2. 向该表中多次插入数据。

   obclient> INSERT INTO t1 VALUES(null);
   

   obclient> INSERT INTO t1 VALUES(3);
   

   obclient> INSERT INTO t1 VALUES(null);
   

3. 插入成功后，查看表中的数据.

   obclient> SELECT * FROM t1;
   

   查询结果如下：

   +-----+
   | c1  |
   +-----+
   |   1 |
   |   3 |
   | 101 |
   +-----+
   

   根据查询结果，发现自增列从 3 跳到了 101。

场景 3：机器重启或宕机

自增列的缓存是一个内存结构，如果 OBServer 节点的机器发生了重启或宕机，该机器上未使用完的缓存区间不会写回内部表，这就导致未使用的这部分区间不会再被使用。例如，假设 OBServer1 上初始自增列的缓存区间为 [1,100]，并且已经生成了自增值 1 和 2。此时，如果 OBServer1 发生了宕机，重启后，该机器上的缓存区间就变成了新的区间 [101,200]，同时下一次的自增值为 101，最终自增值的顺序就是 1，2，101，...，即发生了跳变。

ORDER 模式

为了避免 NOORDER 模式中所提到的 多机多分区生成自增值 和 通过 INSERT 语句插入指定的最大值 等比较常用的使用场景下发生自增值跳变的问题，OceanBase 数据库在 V4.x 版本开始新增了 ORDER 模式的自增列，并且该模式为创建表后的默认模式，能更好地兼容 MySQL 数据库。

对于 ORDER 模式的自增列，其内部原理如下图所示。

与 NOORDER 模式相比，ORDER 模式的自增列会在所有 OBServer 节点中选取当前集群的 Leader 作为自增列服务的 Leader，其它作为 Follower 的 OBServer 节点需要通过发送 RPC 请求来从作为 Leader 的 OBServer 节点处申请自增值，而作为 Leader 的 OBServer 节点会从内部表申请自增区间来作为自增缓存。

例如，同样在多机多分区场景中，假设自增列的 auto_increment_cache_size 为 100，当分区表所在的 OBServer 节点 OBServer1、OBServer2 和 OBServer3 分别按以下顺序先后接收到 insert into values (null) 的请求时，它们内部的处理逻辑如下：

1. OBServer1 发现自身不是 Leader，发送 RPC 请求给 OBServer2，OBServer2 从内部表申请一段自增区间 [1,100]，并给 OBServer1 返回一个自增值 1。

2. OBServer2 发现自身是 Leader，并且存在缓存区间 [2,100]，直接生成一个自增值 2。

3. OBServer3 发现自身不是 Leader，发送 RPC 请求给 OBServer2，OBServer2 发现存在缓存区间 [3,100]，并给 OBServer3 返回一个自增值 3。

……

由此可见，ORDER 模式下，由于所有 OBServer 节点都从 Leader 处申请自增值，所以大部分情况下与单机场景一样，系统总是能生成连续的自增值序列。但是，在并发较高的多机场景，ORDER 模式的性能会比 NOORDER 模式差。

对于 ORDER 模式的自增列，虽然已经解决了 多机多分区生成自增值 和 通过 INSERT 语句插入指定的最大值 等场景下自增值跳变的问题，但是在作为 Leader 的 OBServer 节点的机器重启或宕机、发生切主的场景下，仍然会发生自增值的跳变。

场景 1：机器重启或宕机

在 ORDER 模式中，作为 Leader 的 OBServer 节点上保存了内存下的缓存区间，当作为 Leader 的 OBServer 节点的机器发生重启或宕机时，该区间内未使用的自增值不会被继续使用，而是使用新的缓存区间，从而导致自增值发生跳变。

创建自增列

如果在创建表时指定了自增列，则在使用 INSERT 语句插入记录时不需要指定自增列的值，OceanBase 数据库会自动为该列填充值。

如果您在 INSERT 时指定了自增列的值，且 SQL_MODE 的值未设置为 NO_AUTO_VALUE_ON_ZERO，当指定的值为 0 或比当前值的最大值还大时，OceanBase 数据库会用自增列的下一个值填充列的值；当指定的值比当前最大值小时，不影响自增列的下一个值的计算。

创建 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 比当前自增列（id 列）的最大值（4）大，因此向表 t1 再次插入一条 name 列为 G 且不指定 id 列的值时，OceanBase 数据库会自动使用自增列值 10 的下一个值来填充 id 列的值。

查询结果如下：

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;
   

设置和修改自增列的模式

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

示例：

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

  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
  

修改自增字段的取值

您可以使用以下 SQL 语句设置表中自增列的下一个值，可以是增大或者减小。

ALTER TABLE table_name AUTO_INCREMENT [=] int_value;

其中，table_name 为表名，int_value 为表中自增列的下一个值。

示例

下方 SQL 语句将展示如何调整表的自增列下一个取值。

1. 增大自增列的下一个取值。

   1. 创建测试表 tbl1。

      obclient [test]> CREATE TABLE tbl1(id INT AUTO_INCREMENT, name VARCHAR(50));
      

   2. 查看表 tbl1 当前自增字段的取值。

      obclient [test]> SHOW CREATE TABLE tbl1\G
      

      返回结果如下：

      *************************** 1. row ***************************
            Table: tbl1
      Create Table: CREATE TABLE `tbl1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL
      ) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
      1 row in set
      

   3. 向表 tbl1 中插入 5 条数据。

      obclient [test]> INSERT INTO tbl1(name) VALUES('A1'),('A2'),('A3'),('A4'),('A5');
      

      返回结果如下：

      Query OK, 5 rows affected
      Records: 5  Duplicates: 0  Warnings: 0
      

   4. 再次查看表 tbl1 当前自增字段的取值。

      obclient [test]> SHOW CREATE TABLE tbl1\G
      

      返回结果如下：

      *************************** 1. row ***************************
            Table: tbl1
      Create Table: CREATE TABLE `tbl1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL
      ) AUTO_INCREMENT = 6 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
      1 row in set
      

   5. 将表 tbl1 当前自增列的下一个取值改为 10。

      obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 10;
      

   6. 再次查看表 tbl1 当前自增字段的取值。

      obclient [test]> SHOW CREATE TABLE tbl1\G
      

      返回结果如下：

      *************************** 1. row ***************************
            Table: tbl1
      Create Table: CREATE TABLE `tbl1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL
      ) AUTO_INCREMENT = 10 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
      1 row in set
      

2. 减小自增列的下一个取值。

   1. 将表 tbl1 当前自增列的下一个取值改为 8。

      obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 8;
      

   2. 查看表 tbl1 当前自增字段的取值。

      obclient [test]> SHOW CREATE TABLE tbl1\G
      

      返回结果如下：

      *************************** 1. row ***************************
            Table: tbl1
      Create Table: CREATE TABLE `tbl1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL
      ) AUTO_INCREMENT = 8 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
      1 row in set
      

   3. 删除表 tbl1 自增列中小于等于 4 的值。

      obclient [test]> DELETE FROM tbl1 WHERE id <= 4;
      

      返回结果如下：

      Query OK, 4 rows affected
      

   4. 查询表 tbl1 的数据。

      obclient [test]> SELECT * FROM tbl1;
      

      返回结果如下：

      +------+------+
      | id   | name |
      +------+------+
      |    5 | A5   |
      +------+------+
      1 row in set
      

   5. 执行下面 SQL 语句，将表 tbl1 当前自增列的下一个取值改为 3。

      obclient [test]> ALTER TABLE tbl1 AUTO_INCREMENT = 3;
      

      返回结果如下：

      Query OK, 0 rows affected
      

   6. 再次查看表 tbl1 当前自增字段的取值。

      obclient [test]> SHOW CREATE TABLE tbl1\G
      

      返回结果如下：

      *************************** 1. row ***************************
            Table: tbl1
      Create Table: CREATE TABLE `tbl1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL
      ) AUTO_INCREMENT = 6 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
      1 row in set
      

      说明

      通过查询结果可以观察到，AUTO_INCREMENT 的值为 6。这是由于表中的自增列已经包含数据，且当前自增列的最大值（5）超过了尝试设置的较小值（3）。因此，尽管减小自增值的命令执行没有报错，但实际的 AUTO_INCREMENT 值自动调整为现有最大自增值的下一个数字，即 6。

相关文档

ALTER TABLE