关于自增列 · k8s原理与实践

# 关于自增列如果创建表时需要某个数值列的值不重复并且保持递增，这就是自增列。在 MySQL 租户里，列的类型可以定义为 AUTO\_INCREMENT，即 MySQL 租户的自增列。自增列有三个重要属性：自增起始值、自增步长、自增列缓存大小，通过以下三个租户变量参数控制。 ~~~ 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) ~~~ **示例：CREATE TABLE 使用自增列** 下面创建了一个自增列，在使用 INSERT 插入记录的时候不需要指定自增列，OceanBase 数据库会自动为该列填充值。 ~~~ 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) ~~~ 假如在 INSERT 时指定了自增列的值，如果这个值是0，则 OceanBase 数据库会用自增列的下一个值填充列的值；如果这个值比当前最大值小，则不影响自增列的下一个值的计算；如果这个值比当前值最大值大，则自增列会把插入值和自增列缓存值的和作为下次自增的起始值。 ~~~ 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) ~~~