This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit d33f3be436a7c95c853e1dc0180b2764ea376af4 Author: abmdocrt <[email protected]> AuthorDate: Sat Feb 3 20:17:04 2024 +0800 [Doc](auto-inc) Add doc for auto increment start value (#30741) --- docs/en/docs/advanced/auto-increment.md | 53 ++++++++++++++-------- .../Create/CREATE-TABLE.md | 7 +-- docs/zh-CN/docs/advanced/auto-increment.md | 53 ++++++++++++++-------- .../Create/CREATE-TABLE.md | 5 +- 4 files changed, 75 insertions(+), 43 deletions(-) diff --git a/docs/en/docs/advanced/auto-increment.md b/docs/en/docs/advanced/auto-increment.md index 98f1ee8e21f..9cc51cae8c0 100644 --- a/docs/en/docs/advanced/auto-increment.md +++ b/docs/en/docs/advanced/auto-increment.md @@ -48,7 +48,7 @@ Doris ensures that the values generated on the auto-increment column are dense, ## Syntax -To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to the corresponding column during table creation ([CREATE-TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)). +To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to the corresponding column during table creation ([CREATE-TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)). To manually specify the starting value for an auto-increment column, you can do so by using the `AUTO_INCREMENT(start_value)` statement when creating the table. If not specified, the default starting value is 1. ### Examples @@ -65,7 +65,21 @@ To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to "replication_allocation" = "tag.location.default: 3" ); -2. Creating a Duplicate table with one value column as an auto-increment column: +2. Creating a Duplicate table with one key column as an auto-increment column, and set start value is 100: + + ```sql + CREATE TABLE `demo`.`tbl` ( + `id` BIGINT NOT NULL AUTO_INCREMENT(100), + `value` BIGINT NOT NULL + ) ENGINE=OLAP + DUPLICATE KEY(`id`) + DISTRIBUTED BY HASH(`id`) BUCKETS 10 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 3" + ); + ``` + +3. Creating a Duplicate table with one value column as an auto-increment column: ```sql CREATE TABLE `demo`.`tbl` ( @@ -81,7 +95,7 @@ To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to ); ``` -3. Creating a Unique tbl table with one key column as an auto-increment column: +4. Creating a Unique tbl table with one key column as an auto-increment column: ```sql CREATE TABLE `demo`.`tbl` ( @@ -97,7 +111,7 @@ To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to ); ``` -4. Creating a Unique tbl table with one value column as an auto-increment column: +5. Creating a Unique tbl table with one value column as an auto-increment column: ```sql CREATE TABLE `demo`.`tbl` ( @@ -117,6 +131,7 @@ To use auto-increment columns, you need to add the `AUTO_INCREMENT` attribute to - Only Duplicate model tables and Unique model tables can contain auto-increment columns. - A table can contain at most one auto-increment column. - The type of the auto-increment column must be BIGINT and must be NOT NULL. +- The manually specified starting value for an auto-increment column must be greater than or equal to 0. ## Usage @@ -149,9 +164,9 @@ mysql> select * from tbl order by id; +------+-------+-------+ | id | name | value | +------+-------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +------+-------+-------+ 3 rows in set (0.05 sec) ``` @@ -173,11 +188,11 @@ mysql> select * from tbl order by id; +------+-------+-------+ | id | name | value | +------+-------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | -| 3 | Tom | 40 | -| 4 | John | 50 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +| 4 | Tom | 40 | +| 5 | John | 50 | +------+-------+-------+ 5 rows in set (0.04 sec) ``` @@ -193,13 +208,13 @@ mysql> select * from tbl order by id; +------+---------+-------+ | id | name | value | +------+---------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | -| 3 | Tom | 40 | -| 4 | John | 50 | -| 5 | Doris | 60 | -| 6 | Nereids | 70 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +| 4 | Tom | 40 | +| 5 | John | 50 | +| 6 | Doris | 60 | +| 7 | Nereids | 70 | +------+---------+-------+ 7 rows in set (0.04 sec) ``` diff --git a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md index d2d68aa292d..cfa612bca4d 100644 --- a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md +++ b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md @@ -58,7 +58,7 @@ Column definition list: Column definition: - `column_name column_type [KEY] [aggr_type] [NULL] [AUTO_INCREMENT] [default_value] [on update current_timestamp] [column_comment]` + `column_name column_type [KEY] [aggr_type] [NULL] [AUTO_INCREMENT(auto_inc_start_value)] [default_value] [on update current_timestamp] [column_comment]` * `column_type` @@ -115,11 +115,12 @@ Column definition list: BITMAP_UNION: The aggregation mode of BIMTAP type columns, which performs the union aggregation of bitmaps. ``` - * `AUTO_INCREMENT`(only avaliable in master branch) + * `AUTO_INCREMENT(auto_inc_start_value)`(Available from version 2.1 and later.) To indicate if the column is a auto-increment column. Auto-increment column can be used to generate a unique identity for new row. If no values are assgined for auto-increment column when inserting, Doris will generate sequence numbers automatically. You can also assign the auto-increment column with NULL literal to indicate Doris to generate sequence numbers. It should be noted that, for performance reasons, BE will cache some values of auto-increment column in memory. Therefore [...] A table can have at most one auto-incremnt column. The auto-increment column should be BIGINT type and be NOT NULL. - Both Duplicate model table and Unique model table support auto-increment column + Both Duplicate model table and Unique model table support auto-increment column. + You can specify the starting value for an auto-increment column by providing `auto_inc_start_value. If not specified, the default starting value is 1. * `default_value` diff --git a/docs/zh-CN/docs/advanced/auto-increment.md b/docs/zh-CN/docs/advanced/auto-increment.md index 140546299f8..5a5129348ea 100644 --- a/docs/zh-CN/docs/advanced/auto-increment.md +++ b/docs/zh-CN/docs/advanced/auto-increment.md @@ -49,7 +49,7 @@ Doris保证自增列上自动生成的值是稠密的，但**不能保证**在 ## 语法 -要使用自增列，需要在建表[CREATE-TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)时为对应的列添加`AUTO_INCREMENT`属性。 +要使用自增列，需要在建表[CREATE-TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)时为对应的列添加`AUTO_INCREMENT`属性。 若要手动指定自增列起始值，可以通过建表时`AUTO_INCREMENT(start_value)`语句指定，如果未指定，则默认起始值为1。 ### 示例 @@ -67,7 +67,21 @@ Doris保证自增列上自动生成的值是稠密的，但**不能保证**在 ); ``` -2. 创建一个Dupliciate模型表，其中一个value列是自增列 +2. 创建一个Dupliciate模型表，其中一个key列是自增列，并设置起始值为100 + + ```sql + CREATE TABLE `demo`.`tbl` ( + `id` BIGINT NOT NULL AUTO_INCREMENT(100), + `value` BIGINT NOT NULL + ) ENGINE=OLAP + DUPLICATE KEY(`id`) + DISTRIBUTED BY HASH(`id`) BUCKETS 10 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 3" + ); + ``` + +3. 创建一个Dupliciate模型表，其中一个value列是自增列 ```sql CREATE TABLE `demo`.`tbl` ( @@ -83,7 +97,7 @@ Doris保证自增列上自动生成的值是稠密的，但**不能保证**在 ); ``` -3. 创建一个Unique模型表，其中一个key列是自增列 +4. 创建一个Unique模型表，其中一个key列是自增列 ```sql CREATE TABLE `demo`.`tbl` ( @@ -99,7 +113,7 @@ Doris保证自增列上自动生成的值是稠密的，但**不能保证**在 ); ``` -4. 创建一个Unique模型表，其中一个value列是自增列 +5. 创建一个Unique模型表，其中一个value列是自增列 ```sql CREATE TABLE `demo`.`tbl` ( @@ -119,6 +133,7 @@ Doris保证自增列上自动生成的值是稠密的，但**不能保证**在 1. 仅Duplicate模型表和Unique模型表可以包含自增列。 2. 一张表最多只能包含一个自增列。 3. 自增列的类型必须是BIGINT类型，且必须为NOT NULL。 +4. 自增列手动指定的起始值必须大于等于0。 ## 使用方式 @@ -150,9 +165,9 @@ mysql> select * from tbl order by id; +------+-------+-------+ | id | name | value | +------+-------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +------+-------+-------+ 3 rows in set (0.05 sec) ``` @@ -174,11 +189,11 @@ mysql> select * from tbl order by id; +------+-------+-------+ | id | name | value | +------+-------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | -| 3 | Tom | 40 | -| 4 | John | 50 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +| 4 | Tom | 40 | +| 5 | John | 50 | +------+-------+-------+ 5 rows in set (0.04 sec) ``` @@ -194,13 +209,13 @@ mysql> select * from tbl order by id; +------+---------+-------+ | id | name | value | +------+---------+-------+ -| 0 | Bob | 10 | -| 1 | Alice | 20 | -| 2 | Jack | 30 | -| 3 | Tom | 40 | -| 4 | John | 50 | -| 5 | Doris | 60 | -| 6 | Nereids | 70 | +| 1 | Bob | 10 | +| 2 | Alice | 20 | +| 3 | Jack | 30 | +| 4 | Tom | 40 | +| 5 | John | 50 | +| 6 | Doris | 60 | +| 7 | Nereids | 70 | +------+---------+-------+ 7 rows in set (0.04 sec) ``` diff --git a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md index 9eba3f809af..e288dea49a5 100644 --- a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md +++ b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE.md @@ -56,7 +56,7 @@ distribution_desc * `column_definition` 列定义： - `column_name column_type [KEY] [aggr_type] [NULL] [AUTO_INCREMENT] [default_value] [on update current_timestamp] [column_comment]` + `column_name column_type [KEY] [aggr_type] [NULL] [AUTO_INCREMENT(auto_inc_start_value)] [default_value] [on update current_timestamp] [column_comment]` * `column_type` 列类型，支持以下类型： ``` @@ -106,11 +106,12 @@ distribution_desc HLL_UNION：HLL 类型的列的聚合方式，通过 HyperLogLog 算法聚合。 BITMAP_UNION：BIMTAP 类型的列的聚合方式，进行位图的并集聚合。 ``` - * `AUTO_INCREMENT`(仅在master分支可用) + * `AUTO_INCREMENT(auto_inc_start_value)`(2.1版本及以后可用) 是否为自增列，自增列可以用来为新插入的行生成一个唯一标识。在插入表数据时如果没有指定自增列的值，则会自动生成一个合法的值。当自增列被显示地插入NULL时，其值也会被替换为生成的合法值。需要注意的是，处于性能考虑，BE会在内存中缓存部分自增列的值，所以自增列自动生成的值只能保证单调性和唯一性，无法保证严格的连续性。 一张表中至多有一个列是自增列，自增列必须是BIGINT类型，且必须为NOT NULL。 Duplicate模型表和Unique模型表均支持自增列。 + 可以通过给定`auto_inc_start_value`的方式指定自增列的起始值，如果不指定，则默认起始值为1。 * `default_value` 列默认值，当导入数据未指定该列的值时，系统将赋予该列default_value。 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
