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

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

    1. obclient> show variables where variable_name in ('auto_increment_increment','auto_increment_offset','auto_increment_cache_size');
    2. +---------------------------+---------+
    3. | Variable_name | Value |
    4. +---------------------------+---------+
    5. | auto_increment_cache_size | 1000000 |
    6. | auto_increment_increment | 1 |
    7. | auto_increment_offset | 1 |
    8. +---------------------------+---------+
    9. 3 rows in set (0.01 sec)

    示例:CREATE TABLE 使用自增列

    下面创建了一个自增列,在使用 INSERT 插入记录的时候不需要指定自增列,OceanBase 数据库会自动为该列填充值。

    1. obclient> create table t1(id bigint not null auto_increment primary key, name varchar(50), gmt_create timestamp not null default current_timestamp);
    2. Query OK, 0 rows affected (0.08 sec)
    3. obclient> insert into t1(name) values('A'),('B'),('C');
    4. Query OK, 3 rows affected (0.01 sec)
    5. Records: 3 Duplicates: 0 Warnings: 0
    6. obclient> select * from t1;
    7. +----+------+---------------------+
    8. | id | name | gmt_create |
    9. +----+------+---------------------+
    10. | 1 | A | 2020-04-03 17:09:55 |
    11. | 2 | B | 2020-04-03 17:09:55 |
    12. | 3 | C | 2020-04-03 17:09:55 |
    13. +----+------+---------------------+
    14. 3 rows in set (0.01 sec)

    假如在 INSERT 时指定了自增列的值,如果这个值是0,则 OceanBase 数据库会用自增列的下一个值填充列的值;如果这个值比当前最大值小,则不影响自增列的下一个值的计算;如果这个值比当前值最大值大,则自增列会把插入值和自增列缓存值的和作为下次自增的起始值。

    1. obclient> insert into t1(id, name) values(0, 'D');
    2. Query OK, 1 row affected (0.00 sec)
    3. obclient> insert into t1(id, name) values(-1,'E');
    4. Query OK, 1 row affected (0.00 sec)
    5. obclient> insert into t1(id, name) values(10,'F');
    6. Query OK, 1 row affected (0.01 sec)
    7. obclient> insert into t1(name) values('G');
    8. Query OK, 1 row affected (0.00 sec)
    9. obclient> select * from t1;
    10. +---------+------+---------------------+
    11. | id | name | gmt_create |
    12. +---------+------+---------------------+
    13. | -1 | E | 2020-04-03 17:10:24 |
    14. | 1 | A | 2020-04-03 17:09:55 |
    15. | 2 | B | 2020-04-03 17:09:55 |
    16. | 3 | C | 2020-04-03 17:09:55 |
    17. | 4 | D | 2020-04-03 17:10:19 |
    18. | 10 | F | 2020-04-03 17:10:29 |
    19. | 1000011 | G | 2020-04-03 17:10:34 |
    20. +---------+------+---------------------+
    21. 7 rows in set (0.00 sec)