[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) ~~~