定义自增列 · k8s原理与实践

[TOC] # 定义自增列 ## 描述如果创建表时需要某个数值列的值不重复并且保持递增，这就是自增列。在 MySQL 租户里，列的类型可以定义为`AUTO_INCREMENT`，即 MySQL 租户的自增列。自增列有三个重要属性，包括自增起始值、自增步长和自增列缓存大小，通过以下三个租户变量参数来控制。 <table id="table-591-zff-hgf" class="table"><colgroup colnum="1" colname="col1" colwidth="1*" id="colgroup-2mc-g4r-e3y" style="width:33.33333333333333%"></colgroup><colgroup colnum="2" colname="col2" colwidth="2*" id="colgroup-o2t-3np-88p" style="width:66.66666666666666%"></colgroup><thead id="thead-lxh-00u-8d1" class="thead"><tr id="tr-oor-ewh-61t"><th id="td-yi7-ep4-b8v"><p id="p-8u3-t9p-4zl">系统变量</p></th><th id="td-pje-yau-b3j"><p id="p-x89-qjo-268">说明</p></th></tr></thead><tbody id="tbody-1sm-zen-qiw" class="tbody"><tr id="tr-1gi-ve6-s5y"><td id="td-wes-02i-2mc"><p id="p-nh1-4w6-0hp">auto_increment_cache_size</p></td><td id="td-u61-ovn-oha"><p id="p-vuj-2rh-qug">用于设置自增的缓存个数，取值范围为 [1, 100000000]，默认值为 1000000。</p></td></tr><tr id="tr-sbv-as5-r45"><td id="td-2wc-2ct-y2h"><p id="p-cyg-lrh-oy5">auto_increment_increment</p></td><td id="td-1xr-3uc-ucl"><p id="p-6t3-wpy-lyl">用于设置自增步长，取值范围为 [1, 65535]，默认值为 1。</p></td></tr><tr id="tr-rnh-akv-aj8"><td id="td-bj3-y82-dea"><p id="p-7n0-uy1-f7y">auto_increment_offset</p></td><td id="td-k6k-6wr-57h"><p id="p-uwc-lew-n25">用于确定<code data-tag="code" class="code">AUTO_INCREMENT</code>列值的起点，取值范围为 [1, 65535]，默认值为 1。</p></td></tr></tbody></table> ## 示例下面创建了一个自增列，在使用`INSERT`语句插入记录时不需要指定自增列，OceanBase 数据库会自动为该列填充值。如果在`INSERT`时指定了自增列的值，且这个值为 0，则 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) 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) ~~~