關于自增列 · 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) ~~~