跳到主要内容
版本：Candidate-3.4

AUTO_INCREMENT

StarRocks 自 3.0 版本起支持 AUTO_INCREMENT 列属性，可以简化数据管理。本文介绍 AUTO_INCREMENT 列属性的应用场景、用法和特性。

功能介绍​

当插入一条新的记录时，StarRocks 会自动为该记录的自增列分配一个表内全局唯一的整数值作为自增 ID，并且后续值会自动增加。自增列可以简化数据管理，同时可以加速一些查询场景。以下是一些自增列的应用场景：

  • 主键：自增列可用于生成主键，确保每条记录都有一个唯一的标识符，方便查询和管理数据。
  • 关联表：在多个表之间进行关联时，可以使用自增列作为 Join Key，相比使用如 UUID 等字符串类型的列能够提高查询速度。
  • 高基数列的精确去重计数：将自增列的 ID 值作为字典唯一值列，相比用字符串直接精确去重计数，查询速度能提升数倍甚至十数倍。

您需要在 CREATE TABLE 语句中通过 AUTO_INCREMENT 属性指定自增列。自增列的数据类型只支持 BIGINT，从 1 开始增加，自增步长为 1。 并且 StarRocks 支持隐式分配自增列的值和显式指定自增 ID。

基本用法​

建表指定自增列​

创建表 test_tbl1，包含两列，分别为 id 和 number，如下所示，建表时指定 number 列为自增列：

CREATE TABLE test_tbl1
(
    id BIGINT NOT NULL, 
    number BIGINT NOT NULL AUTO_INCREMENT
) 
PRIMARY KEY (id) 
DISTRIBUTED BY HASH(id)
PROPERTIES("replicated_storage" = "true");

分配自增列的值​

隐式分配自增列的值

导入时，您无需指定自增列的值，StarRocks 会自动为该自增列分配唯一的整数值，并插入到表中。

INSERT INTO test_tbl1 (id) VALUES (1);
INSERT INTO test_tbl1 (id) VALUES (2);
INSERT INTO test_tbl1 (id) VALUES (3),(4),(5);

查看表的数据。

mysql > SELECT * FROM test_tbl1 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    3 |      3 |
|    4 |      4 |
|    5 |      5 |
+------+--------+
5 rows in set (0.02 sec)

您也可以指定自增列的值为 DEFAULT，StarRocks 会自动为该自增列分配唯一的整数值，并插入到表中。

INSERT INTO test_tbl1 (id, number) VALUES (6, DEFAULT);

查看表的数据。

mysql > SELECT * FROM test_tbl1 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    3 |      3 |
|    4 |      4 |
|    5 |      5 |
|    6 |      6 |
+------+--------+
6 rows in set (0.02 sec)

在实际使用中，您查看表的数据时可能会返回如下结果。这是因为 StarRocks 无法保证自增列的值按照时间顺序严格递增，但是能保证自增列的值大致上是递增的。更多介绍，请参见单调性保证。

mysql > SELECT * FROM test_tbl1 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 | 100001 |
|    3 | 200001 |
|    4 | 200002 |
|    5 | 200003 |
|    6 | 200004 |
+------+--------+
6 rows in set (0.01 sec)

显式指定自增列的值

您也可以显式地指定自增列的值，并插入到表中。

INSERT INTO test_tbl1 (id, number) VALUES (7, 100);

-- 查看表的数据
mysql > SELECT * FROM test_tbl1 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 | 100001 |
|    3 | 200001 |
|    4 | 200002 |
|    5 | 200003 |
|    6 | 200004 |
|    7 |    100 |
+------+--------+
7 rows in set (0.01 sec)

并且，后续插入新数据时不会影响 StarRocks 新生成的自增列的值。

INSERT INTO test_tbl1 (id) VALUES (8);

-- 查看表的数据
mysql > SELECT * FROM test_tbl1 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 | 100001 |
|    3 | 200001 |
|    4 | 200002 |
|    5 | 200003 |
|    6 | 200004 |
|    7 |    100 |
|    8 |      2 |
+------+--------+
8 rows in set (0.01 sec)

注意事项

因为同时隐式分配和显式指定自增 ID 可能会破坏自增 ID 的全局唯一性，建议您不要混用。

基本特性​

唯一性保证​

在一般情况下，StarRocks 保证自增 ID 在一张表内是全局唯一的。

但是，如果您混用隐式分配和显式指定自增 ID，则可能会破坏自增 ID 的全局唯一性。因此建议您不要同时隐式分配和显式指定自增 ID。以下是一个简单的示例：

创建表 test_tbl2，其中列 number 为自增列。

CREATE TABLE test_tbl2
(
    id BIGINT NOT NULL,
    number BIGINT NOT NULL AUTO_INCREMENT
 ) 
PRIMARY KEY (id) 
DISTRIBUTED BY HASH(id)
PROPERTIES("replicated_storage" = "true");

往表 test_tbl2 中同时隐式分配和显式指定自增 ID。

INSERT INTO test_tbl2 (id, number) VALUES (1, DEFAULT);
INSERT INTO test_tbl2 (id, number) VALUES (2, 2);
INSERT INTO test_tbl2 (id) VALUES (3);

查询表 test_tbl2 的数据。

mysql > SELECT * FROM test_tbl2 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 |      2 |
|    3 | 100001 |
+------+--------+
3 rows in set (0.08 sec)

单调性保证​

为了提升分配自增 ID 的性能，BE 会本地缓存部分自增 ID。在这种情况下，StarRocks 无法保证自增 ID 按照时间顺序严格递增，只能保证自增 ID 大致上是递增的。

说明

BE 缓存的自增 ID 数量由 FE 动态参数 auto_increment_cache_size 决定，默认是 100000。您可以使用 ADMIN SET FRONTEND CONFIG ("auto_increment_cache_size" = "xxx"); 进行修改 。 假设 StarRocks 集群具有一个 FE 节点和两个 BE 节点。创建表 test_tbl3 并且插入五行数据，如下所示：

CREATE TABLE test_tbl3
(
    id BIGINT NOT NULL,
    number BIGINT NOT NULL AUTO_INCREMENT
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES("replicated_storage" = "true");

INSERT INTO test_tbl3 VALUES (1, DEFAULT);
INSERT INTO test_tbl3 VALUES (2, DEFAULT);
INSERT INTO test_tbl3 VALUES (3, DEFAULT);
INSERT INTO test_tbl3 VALUES (4, DEFAULT);
INSERT INTO test_tbl3 VALUES (5, DEFAULT);

表 test_tbl3` 中自增 ID 不是单调递增的。这是因为两个 BE 节点分别缓存了 [1, 100000] 和 [100001, 200000] 范围内的自增 ID，使用多个 INSERT 语句导入数据时，会发送给不同的 BE，由不同 BE 分配自增 ID，因此无法保证自增 ID 的严格单调性。

mysql > SELECT * FROM test_tbl3 ORDER BY id;
+------+--------+
| id   | number |
+------+--------+
|    1 |      1 |
|    2 | 100001 |
|    3 | 200001 |
|    4 |      2 |
|    5 | 100002 |
+------+--------+
5 rows in set (0.07 sec)

部分列更新与自增列​

本小节介绍具有自增列的表如何实现部分列更新。

说明

目前仅主键表支持部分列更新。

自增列为主键​

如果自增列为主键或主键的一部分，由于部分列更新时您需要指定主键，因此部分列更新的用户行为和没有定义自增列完全一样。

  1. 在数据库 example_db 中创建表 test_tbl4，并且插入一条数据。

    -- 建表
    CREATE TABLE test_tbl4
    (
        id BIGINT AUTO_INCREMENT,
        name BIGINT NOT NULL,
        job1 BIGINT NOT NULL,
        job2 BIGINT NOT NULL
    ) 
    PRIMARY KEY (id, name)
    DISTRIBUTED BY HASH(id)
    PROPERTIES("replicated_storage" = "true");

    -- 准备数据
    mysql > INSERT INTO test_tbl4 (id, name, job1, job2) VALUES (0, 0, 1, 1);
    Query OK, 1 row affected (0.04 sec)
    {'label':'insert_6af28e77-7d2b-11ed-af6e-02424283676b', 'status':'VISIBLE', 'txnId':'152'}

    -- 查询数据
    mysql > SELECT * FROM test_tbl4 ORDER BY id;
    +------+------+------+------+
    | id   | name | job1 | job2 |
    +------+------+------+------+
    |    0 |    0 |    1 |    1 |
    +------+------+------+------+
    1 row in set (0.01 sec)

  2. 准备 CSV 文件 my_data4.csv，用于更新表 test_tbl4。 CSV 文件包括自增列的 ID 值，不包含列 job1 的值，并且第一行数据的主键存在表 test_tbl4 中，第二行的主键不存在。

    0,0,99
    1,1,99

  3. 通过 Stream Load 将 CSV 文件数据更新至表 test_tbl4。

    curl --location-trusted -u <username>:<password> -H "label:1" \
        -H "column_separator:," \
        -H "partial_update:true" \
        -H "columns:id,name,job2" \
        -T my_data4.csv -XPUT \
        http://<fe_host>:<fe_http_port>/api/example_db/test_tbl4/_stream_load

  4. 查询更新后的表。第一条数据原先已经存在表 test_tbl4 中，并且列 job1 保持原先的值。第二条数据是新插入的数据，由于列 job1 没有定义默认值，因此部分列更新框架会直接将此列的值设置为 0。

    mysql > SELECT * FROM test_tbl4 ORDER BY id;
    +------+------+------+------+
    | id   | name | job1 | job2 |
    +------+------+------+------+
    |    0 |    0 |    1 |   99 |
    |    1 |    1 |    0 |   99 |
    +------+------+------+------+
    2 rows in set (0.01 sec)

自增列不为主键​

如果自增列不是主键或者主键的一部分，并且 Stream Load 中未给出自增 ID，则会发生以下情况：

  • 如果表中已经存在该行，则 StarRocks 不会更新自增ID。
  • 如果表中不存在该行，则 StarRocks 会自动生成新的自增 ID。

该特性可以构建字典表的值，用于加速字符串的精确去重计数。

  1. 在数据库 example_db 中创建表 test_tbl5，指定 job1 为自增列，并且插入一条数据。

    -- 建表
    CREATE TABLE test_tbl5
    (
        id BIGINT NOT NULL,
        name BIGINT NOT NULL,
        job1 BIGINT NOT NULL AUTO_INCREMENT,
        job2 BIGINT NOT NULL
    )
    PRIMARY KEY (id, name)
    DISTRIBUTED BY HASH(id)
    PROPERTIES("replicated_storage" = "true");

    -- 准备数据
    mysql > INSERT INTO test_tbl5 VALUES (0, 0, -1, -1);
    Query OK, 1 row affected (0.04 sec)
    {'label':'insert_458d9487-80f6-11ed-ae56-aa528ccd0ebf', 'status':'VISIBLE', 'txnId':'94'}

    mysql > SELECT * FROM test_tbl5 ORDER BY id;
    +------+------+------+------+
    | id   | name | job1 | job2 |
    +------+------+------+------+
    |    0 |    0 |   -1 |   -1 |
    +------+------+------+------+
    1 row in set (0.01 sec)

  2. 准备 CSV 文件 my_data5.csv，用于更新表 test_tbl5。CSV 文件不包含自增列 job1 的值，并且第一行数据的主键存在于表中，第二、三行数据的主键不存在。

    0,0,99
    1,1,99
    2,2,99

  3. 通过 Stream Load 将 CSV 文件数据导入至表 test_tbl5。

    curl --location-trusted -u <username>:<password> -H "label:2" \
        -H "column_separator:," \
        -H "partial_update:true" \
        -H "columns: id,name,job2" \
        -T my_data5.csv -XPUT \
        http://<fe_host>:<fe_http_port>/api/example_db/test_tbl5/_stream_load

  4. 查询更新后的表。第一条数据已经存在表 test_tbl5 中，自增列 job1 保持原先的 ID 值。第二、三条数据是新插入的数据，自增列 job1 的 ID 值由 StarRocks 自动生成。

    mysql > SELECT * FROM test_tbl5 ORDER BY id;
    +------+------+--------+------+
    | id   | name | job1   | job2 |
    +------+------+--------+------+
    |    0 |    0 |     -1 |   99 |
    |    1 |    1 |      1 |   99 |
    |    2 |    2 | 100001 |   99 |
    +------+------+--------+------+
    3 rows in set (0.01 sec)

使用限制​

  • 创建具有自增列的表时，必须设置 'replicated_storage' = 'true'，以确保所有副本具有相同的自增 ID。
  • 每个表最多只能有一个自增列。
  • 自增列必须是 BIGINT 类型。
  • 自增列必须为 NOT NULL，并且不支持指定默认值。
  • 您可以从具有自增列的主键表中删除数据。但是如果自增列不为 Primary Key，则您在删除数据时，需要注意以下两个场景中的限制：
    • DELETE 操作的同时，还存在一个部分列更新的导入任务，其中只包含 UPSERT 操作。如果 UPSERT 操作和 DELETE 操作命中了同一行数据，并且 UPSERT 操作在 DELETE 操作后执行，则该 UPSERT 操作可能会失效。
    • 存在一个部分列更新的导入任务，其中包含若干个对同一行数据的 UPSERT、DELETE 操作。如果某个 UPSERT 操作在 DELETE 操作后执行，则该 UPSERT 操作可能会失效。
  • 不支持使用 ALTER TABLE 添加 AUTO_INCREMENT 属性。
  • 自 3.1 版本起存算分离模式支持该功能。
  • 不支持设置自增列的起始值和自增步长。

Keywords​

AUTO_INCREMENT, AUTO INCREMENT