This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new ffae2a74863 [Doc](auto-inc) Add doc for auto increment start value
(#30741)
ffae2a74863 is described below
commit ffae2a7486364b2b967eda24be1699356c61f7cb
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]
