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 a8f46cab75e66f97ed0c89546500bc75e014be7e
Author: yuanyuan8983 <99315889+yuanyuan8...@users.noreply.github.com>
AuthorDate: Wed Jan 31 14:47:27 2024 +0800
[typo](doc) Modify the alter table column document (#28645)
---
.../Alter/ALTER-TABLE-COLUMN.md | 398 ++++++++++++--------
.../Alter/ALTER-TABLE-COLUMN.md | 414 ++++++++++++---------
2 files changed, 478 insertions(+), 334 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
index 3e477cead22..1276ccddbfa 100644
---
a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
+++
b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
@@ -24,9 +24,7 @@ specific language governing permissions and limitations
under the License.
-->
-## ALTER-TABLE-COLUMN
-
-### Name
+## Name
ALTER TABLE COLUMN
@@ -34,7 +32,13 @@ ALTER TABLE COLUMN
This statement is used to perform a schema change operation on an existing
table. The schema change is asynchronous, and the task is returned when the
task is submitted successfully. After that, you can use the [SHOW ALTER TABLE
COLUMN](../../Show-Statements/SHOW-ALTER.md) command to view the progress.
-grammar:
+Doris has the concept of materialized index after table construction. After
successful table construction, it is the base table and the materialized index
is the base index. rollup index can be created based on the base table. Both
base index and rollup index are materialized indexes. If rollup_index_name is
not specified during the schema change operation, the operation is based on the
base table by default.
+
+:::note
+Doris 1.2.0 supports light schema change for light scale structure changes,
and addition and subtraction operations for value columns can be completed more
quickly and synchronously. You can manually specify "light_schema_change" =
'true' when creating a table. This parameter is enabled by default for versions
2.0.0 and later.
+:::
+
+### Grammar:
```sql
ALTER TABLE [database.]table alter_clause;
@@ -42,186 +46,256 @@ ALTER TABLE [database.]table alter_clause;
The alter_clause of schema change supports the following modification methods:
-1. Add a column to the specified position at the specified index
+**1. Add a column to the specified position at the specified index**
-grammar:
+**Grammar**
```sql
-ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
+ALTER TABLE [database.]table table_name ADD COLUMN column_name column_type
[KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]
```
-
- Notice:
-
+
+**Example**
+
+1. Add a key column new_col to example_db.my_table after key_1 (non-aggregated
model)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;
+ ```
+
+2. Add a value column new_col to example_db.my_table after value_1
(non-aggregate model)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT DEFAULT "0" AFTER value_1;
+ ```
+
+3. Add a key column new_col (aggregate model) to example_db.my_table after
key_1
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;
+ ```
+
+4. Add a value column to example_db.my_table after value_1 new_col SUM
Aggregation type (aggregation model)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT SUM DEFAULT "0" AFTER value_1;
+ ```
+
+5. Add new_col to the first column position of the example_db.my_table table
(non-aggregated model)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" FIRST;
+ ```
+
+:::note
- If you add a value column to the aggregation model, you need to specify
agg_type
- For non-aggregated models (such as DUPLICATE KEY), if you add a key column,
you need to specify the KEY keyword
-- You cannot add columns that already exist in the base index to the rollup
index (you can recreate a rollup index if necessary)
+- You cannot add columns that already exist in the base index to the rollup
index (you can recreate a rollup index if necessary) :::
-2. Add multiple columns to the specified index
-grammar:
+**2. Add multiple columns to the specified index**
+**Grammar**
+
```sql
-ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value",
...)
+ALTER TABLE [database.]table table_name ADD COLUMN (column_name1 column_type
[KEY | agg_type] DEFAULT "default_value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]
```
-Notice:
-
-- If you add a value column to the aggregation model, you need to specify
agg_type
-- If you add a key column to the aggregation model, you need to specify the
KEY keyword
-- You cannot add columns that already exist in the base index to the rollup
index (you can recreate a rollup index if necessary)
-
-3. Delete a column from the specified index
-
-grammar:
-
+
+**Example**
+
+1. Add multiple columns to example_db.my_table, where new_col and new_col2 are
SUM aggregate types (aggregate model)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN (new_col1 INT SUM DEFAULT "0" ,new_col2 INT SUM DEFAULT "0");
+ ```
+
+2. Add multiple columns to example_db.my_table (non-aggregated model), where
new_col1 is the KEY column and new_col2 is the value column
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN (new_col1 INT key DEFAULT "0" , new_col2 INT DEFAULT "0");
+ ```
+
+:::note
+ - If you add a value column to the aggregation model, you need to specify
agg_type
+ - If you add a key column to the aggregation model, you need to specify the
KEY keyword
+ - You cannot add columns that already exist in the base index to the rollup
index (you can recreate a rollup index if necessary) :::
+
+**3. Delete a column from the specified index**
+
+**Grammar***
+
+ ```sql
+ ALTER TABLE [database.]table table_name DROP COLUMN column_name
+ [FROM rollup_index_name]
+ ```
+
+**Example**
+
+1. Delete column col1 from example_db.my_table
+
+ ```sql
+ ALTER TABLE example_db.my_table DROP COLUMN col1;
+ ```
+
+:::note
+ - Cannot drop partition column
+ - The aggregate model cannot delete KEY columns
+ - If the column is removed from the base index, it will also be removed if
it is included in the rollup index :::
+
+**4. Modify the column type and column position of the specified index**
+
+**Grammar**
+
```sql
-DROP COLUMN column_name
-[FROM rollup_index_name]
-```
-
-Notice:
-
-- Cannot drop partition column
-- If the column is removed from the base index, it will also be removed if it
is included in the rollup index
-
-4. Modify the column type and column position of the specified index
-
- grammar:
-
-```sql
-MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL]
[DEFAULT "default_value"]
+ALTER TABLE [database.]table table_name MODIFY COLUMN column_name column_type
[KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]
```
-
-Notice:
-
-- If you modify the value column in the aggregation model, you need to specify
agg_type
-- If you modify the key column for non-aggregate types, you need to specify
the KEY keyword
-- Only the type of the column can be modified, and other attributes of the
column remain as they are (that is, other attributes need to be explicitly
written in the statement according to the original attributes, see example 8)
-- Partitioning and bucketing columns cannot be modified in any way
-- The following types of conversions are currently supported (loss of
precision is guaranteed by the user)
- - Conversion of TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE types to
larger numeric types
- - Convert TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL to
VARCHAR
- - VARCHAR supports modifying the maximum length
- - VARCHAR/CHAR converted to TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
- - Convert VARCHAR/CHAR to DATE (currently supports "%Y-%m-%d", "%y-%m-%d",
"%Y%m%d", "%y%m%d", "%Y/%m/%d, "%y/%m/%d" six formats)
- - Convert DATETIME to DATE (only keep year-month-day information, for
example: `2019-12-09 21:47:05` <--> `2019-12-09`)
- - DATE is converted to DATETIME (hours, minutes and seconds are
automatically filled with zeros, for example: `2019-12-09` <--> `2019-12-09
00:00:00`)
- - Convert FLOAT to DOUBLE
- - INT is converted to DATE (if the INT type data is illegal, the conversion
fails, and the original data remains unchanged)
- - All can be converted to STRING except DATE and DATETIME, but STRING cannot
be converted to any other type
-
-5. Reorder the column at the specified index
-
-grammar:
-
-```sql
-ORDER BY (column_name1, column_name2, ...)
-[FROM rollup_index_name]
-[PROPERTIES ("key"="value", ...)]
-```
-
-Notice:
-
-- All columns in index are written out
-- the value column comes after the key column
-
-### Example
-
-1. Add a key column new_col after col1 of example_rollup_index (non-aggregated
model)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-2. Add a value column new_col after col1 of example_rollup_index
(non-aggregation model)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-3. Add a key column new_col (aggregation model) after col1 of
example_rollup_index
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-4. Add a value column new_col SUM aggregation type (aggregation model) after
col1 of example_rollup_index
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-5. Add multiple columns to example_rollup_index (aggregation model)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
-TO example_rollup_index;
-```
-
-6. Remove a column from example_rollup_index
-
-```sql
-ALTER TABLE example_db.my_table
-DROP COLUMN col2
-FROM example_rollup_index;
-```
-
-7. Modify the type of the key column col1 of the base index to BIGINT and move
it to the back of the col2 column.
-
-```sql
-ALTER TABLE example_db.my_table
-MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
-```
-
-Note: Whether you modify the key column or the value column, you need to
declare complete column information
-
-8. Modify the maximum length of the val1 column of base index. The original
val1 is (val1 VARCHAR(32) REPLACE DEFAULT "abc")
-
-```sql
-ALTER TABLE example_db.my_table
-MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
-```
-Note: You can only modify the column's data type; other attributes of the
column must remain unchanged.
-
-9. Reorder the columns in example_rollup_index (set the original column order
as: k1,k2,k3,v1,v2)
-
-```sql
-ALTER TABLE example_db.my_table
-ORDER BY (k3,k1,k2,v2,v1)
-FROM example_rollup_index;
-```
-
-10. Do Two Actions Simultaneously
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
-ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;
-```
-
-11. Modify the length of a field in the Key column of the Duplicate key table
-
-```sql
-alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
-````
-
+
+**Example**
+
+1. Modify the type of the key column col1 of the base index to BIGINT and move
it to the back of the col2 column
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
+ ```
+
+ :::note
+ Whether you modify the key column or the value column, you need to declare
complete column information
+ :::
+
+2. Modify the maximum length of the val1 column of base index. The original
val1 is (val1 VARCHAR(32) REPLACE DEFAULT "abc")
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
+ ```
+
+ :::note
+ You can only modify the column's data type; other attributes of the column
must remain unchanged.
+ :::
+
+3. Modify the length of a field in the Key column of the Duplicate key table
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN k3 VARCHAR(50) KEY NULL COMMENT 'to 50';
+ ```
+
+:::note
+ - If you modify the value column in the aggregation model, you need to
specify agg_type
+ - If you modify the key column for non-aggregate types, you need to specify
the KEY keyword
+ - Only the type of the column can be modified, and other attributes of the
column remain as they are (that is, other attributes need to be explicitly
written in the statement according to the original attributes, see example 8)
+ - Partitioning and bucketing columns cannot be modified in any way
+ - The following types of conversions are currently supported (loss of
precision is guaranteed by the user)
+ - Conversion of TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE types to
larger numeric types
+ - Convert TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL to
VARCHAR
+ - VARCHAR supports modifying the maximum length
+ - VARCHAR/CHAR converted to
TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
+ - Convert VARCHAR/CHAR to DATE (currently supports "%Y-%m-%d", "%y-%m-%d",
"%Y%m%d", "%y%m%d", "%Y/%m/%d, "%y/%m/%d" six formats)
+ - Convert DATETIME to DATE (only keep year-month-day information, for
example: `2019-12-09 21:47:05` <--> `2019-12-09`)
+ - DATE is converted to DATETIME (hours, minutes and seconds are
automatically filled with zeros, for example: `2019-12-09` <--> `2019-12-09
00:00:00`)
+ - Convert FLOAT to DOUBLE
+ - INT is converted to DATE (if the INT type data is illegal, the
conversion fails, and the original data remains unchanged)
+ - All can be converted to STRING except DATE and DATETIME, but STRING
cannot be converted to any other type :::
+
+**5. Reorder the column at the specified index**
+
+**Grammar**
+
+ ```sql
+ ALTER TABLE [database.]table table_name ORDER BY (column_name1,
column_name2, ...)
+ [FROM rollup_index_name]
+ [PROPERTIES ("key"="value", ...)]
+ ```
+
+**Example**
+
+1. Adjust the order of the key and value columns of example_db.my_table
(non-aggregate model)
+
+ ```sql
+ CREATE TABLE `my_table`(
+ `k_1` INT NULL,
+ `k_2` INT NULL,
+ `v_1` INT NULL,
+ `v_2` varchar NULL,
+ `v_3` varchar NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k_1`, `k_2`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ALTER TABLE example_db.my_table ORDER BY (k_2,k_1,v_3,v_2,v_1);
+
+ mysql> desc my_table;
+ +-------+------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+------------+------+-------+---------+-------+
+ | k_2 | INT | Yes | true | NULL | |
+ | k_1 | INT | Yes | true | NULL | |
+ | v_3 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_2 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_1 | INT | Yes | false | NULL | NONE |
+ +-------+------------+------+-------+---------+-------+
+ ```
+
+2. Do two actions simultaneously
+
+ ```sql
+ CREATE TABLE `my_table` (
+ `k_1` INT NULL,
+ `k_2` INT NULL,
+ `v_1` INT NULL,
+ `v_2` varchar NULL,
+ `v_3` varchar NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k_1`, `k_2`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ALTER TABLE example_db.my_table
+ ADD COLUMN col INT DEFAULT "0" AFTER v_1,
+ ORDER BY (k_2,k_1,v_3,v_2,v_1,col);
+
+ mysql> desc my_table;
+ +-------+------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+------------+------+-------+---------+-------+
+ | k_2 | INT | Yes | true | NULL | |
+ | k_1 | INT | Yes | true | NULL | |
+ | v_3 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_2 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_1 | INT | Yes | false | NULL | NONE |
+ | col | INT | Yes | false | 0 | NONE |
+ +-------+------------+------+-------+---------+-------+
+ ```
+
+:::note
+ - All columns in index are written out
+ - the value column comes after the key column
+ - You can adjust the key column only within the range of the key column. The
same applies to the value column
+:::
+
### Keywords
```text
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
index 6394ba62947..db43a0ac8fb 100644
---
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
+++
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md
@@ -24,9 +24,7 @@ specific language governing permissions and limitations
under the License.
-->
-## ALTER-TABLE-COLUMN
-
-### Name
+## Name
ALTER TABLE COLUMN
@@ -34,7 +32,12 @@ ALTER TABLE COLUMN
该语句用于对已有 table 进行 Schema change 操作。schema change 是异步的，任务提交成功则返回，之后可使用[SHOW
ALTER TABLE COLUMN](../../Show-Statements/SHOW-ALTER.md) 命令查看进度。
-语法：
+:::note
+Doris 在建表之后有物化索引的概念，在建表成功后为 base 表，物化索引为 base index，基于 base 表可以创建 rollup
index。其中 base index 和 rollup index 都是物化索引，在进行 schema change 操作时如果不指定
rollup_index_name 默认基于 base 表进行操作。
+Doris 在 1.2.0 支持了 light schema change 轻量表结构变更，对于值列的加减操作，可以更快地，同步地完成。可以在建表时手动指定
"light_schema_change" = 'true'，2.0.0 及之后版本该参数默认开启。
+:::
+
+### 语法：
```sql
ALTER TABLE [database.]table alter_clause;
@@ -42,187 +45,254 @@ ALTER TABLE [database.]table alter_clause;
schema change 的 alter_clause 支持如下几种修改方式：
-1. 向指定 index 的指定位置添加一列
+**1. 添加列，向指定的 index 位置进行列添加**
-语法：
+**语法**
```sql
-ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
+ALTER TABLE [database.]table table_name ADD COLUMN column_name column_type
[KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]
```
-
- 注意：
-
-- 聚合模型如果增加 value 列，需要指定 agg_type
-- 非聚合模型（如 DUPLICATE KEY）如果增加key列，需要指定KEY关键字
-- 不能在 rollup index 中增加 base index 中已经存在的列（如有需要，可以重新创建一个 rollup index）
-
-2. 向指定 index 添加多列
-
-语法：
-
+
+**Example**
+
+1. 向 example_db.my_table 的 key_1 后添加一个 key 列 new_col (非聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;
+ ```
+
+2. 向 example_db.my_table 的 value_1 后添加一个 value 列 new_col (非聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT DEFAULT "0" AFTER value_1;
+ ```
+
+3. 向 example_db.my_table 的 key_1 后添加一个 key 列 new_col (聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;
+ ```
+
+4. 向 example_db.my_table 的 value_1 后添加一个 value 列 new_col SUM 聚合类型 (聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT SUM DEFAULT "0" AFTER value_1;
+ ```
+
+5. 将 new_col 添加到 example_db.my_table 表的首列位置 (非聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN new_col INT KEY DEFAULT "0" FIRST;
+ ```
+
+ :::note
+ - 聚合模型如果增加 value 列，需要指定 agg_type
+ - 非聚合模型（如 DUPLICATE KEY）如果增加key列，需要指定KEY关键字
+ - 不能在 rollup index 中增加 base index 中已经存在的列（如有需要，可以重新创建一个 rollup index）:::
+
+**2. 添加多列，向指定的 index 位置进行多列添加**
+
+**语法**
+
+ ```sql
+ ALTER TABLE [database.]table table_name ADD COLUMN (column_name1 column_type
[KEY | agg_type] DEFAULT "default_value", ...)
+ [TO rollup_index_name]
+ [PROPERTIES ("key"="value", ...)]
+ ```
+
+**Example**
+
+1. 向 example_db.my_table 中添加多列，new_col 和 new_col2 都是 SUM 聚合类型(聚合模型)
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN (new_col1 INT SUM DEFAULT "0" ,new_col2 INT SUM DEFAULT "0");
+ ```
+
+2. 向 example_db.my_table 中添加多列(非聚合模型)，其中 new_col1 为 KEY 列，new_col2 为 value 列
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ ADD COLUMN (new_col1 INT key DEFAULT "0" , new_col2 INT DEFAULT "0");
+ ```
+
+:::note
+
+ - 聚合模型如果增加 value 列，需要指定agg_type
+ - 聚合模型如果增加key列，需要指定KEY关键字
+ - 不能在 rollup index 中增加 base index 中已经存在的列（如有需要，可以重新创建一个 rollup index）:::
+
+**3. 删除列，从指定 index 中删除一列**
+
+**语法**
+
+ ```sql
+ ALTER TABLE [database.]table table_name DROP COLUMN column_name
+ [FROM rollup_index_name]
+ ```
+
+**Example**
+
+1. 从 example_db.my_table 中删除 col1 列
+
+ ```sql
+ ALTER TABLE example_db.my_table DROP COLUMN col1;
+ ```
+
+:::note
+ - 不能删除分区列
+ - 聚合模型不能删除KEY列
+ - 如果是从 base index 中删除列，则如果 rollup index 中包含该列，也会被删除 :::
+
+**4. 修改指定列类型以及列位置**
+
+**语法**
+
```sql
-ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value",
...)
-[TO rollup_index_name]
-[PROPERTIES ("key"="value", ...)]
-```
-
-注意：
-
-- 聚合模型如果增加 value 列，需要指定agg_type
-- 聚合模型如果增加key列，需要指定KEY关键字
-- 不能在 rollup index 中增加 base index 中已经存在的列（如有需要，可以重新创建一个 rollup index）
-
-3. 从指定 index 中删除一列
-
-语法：
-
-```sql
-DROP COLUMN column_name
-[FROM rollup_index_name]
-```
-
-注意：
-
-- 不能删除分区列
-- 如果是从 base index 中删除列，则如果 rollup index 中包含该列，也会被删除
-
-4. 修改指定 index 的列类型以及列位置
-
- 语法：
-
-```sql
-MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL]
[DEFAULT "default_value"]
+ALTER TABLE [database.]table table_name MODIFY COLUMN column_name column_type
[KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]
```
-注意：
-
-- 聚合模型如果修改 value 列，需要指定 agg_type
-- 非聚合类型如果修改key列，需要指定KEY关键字
-- 只能修改列的类型，列的其他属性维持原样（即其他属性需在语句中按照原属性显式的写出，参见 example 8）
-- 分区列和分桶列不能做任何修改
-- 目前支持以下类型的转换（精度损失由用户保证）
- - TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE 类型向范围更大的数字类型转换
- - TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL 转换成 VARCHAR
- - VARCHAR 支持修改最大长度
- - VARCHAR/CHAR 转换成 TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
- - VARCHAR/CHAR 转换成 DATE (目前支持"%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d",
"%Y/%m/%d, "%y/%m/%d"六种格式化格式)
- - DATETIME 转换成 DATE(仅保留年-月-日信息, 例如: `2019-12-09 21:47:05` <--> `2019-12-09`)
- - DATE 转换成 DATETIME(时分秒自动补零， 例如: `2019-12-09` <--> `2019-12-09 00:00:00`)
- - FLOAT 转换成 DOUBLE
- - INT 转换成 DATE (如果INT类型数据不合法则转换失败，原始数据不变)
- - 除DATE与DATETIME以外都可以转换成STRING，但是STRING不能转换任何其他类型
-
-5. 对指定 index 的列进行重新排序
-
-语法：
-
-```sql
-ORDER BY (column_name1, column_name2, ...)
-[FROM rollup_index_name]
-[PROPERTIES ("key"="value", ...)]
-```
-
-注意：
-
-- index 中的所有列都要写出来
-- value 列在 key 列之后
-
-### Example
-
-1. 向 example_rollup_index 的 col1 后添加一个key列 new_col(非聚合模型)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-2. 向example_rollup_index的col1后添加一个value列new_col(非聚合模型)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-3. 向example_rollup_index的col1后添加一个key列new_col(聚合模型)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-4. 向example_rollup_index的col1后添加一个value列new_col SUM聚合类型(聚合模型)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
-TO example_rollup_index;
-```
-
-5. 向 example_rollup_index 添加多列(聚合模型)
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
-TO example_rollup_index;
-```
-
-6. 从 example_rollup_index 删除一列
-
-```sql
-ALTER TABLE example_db.my_table
-DROP COLUMN col2
-FROM example_rollup_index;
-```
-
-7. 修改 base index 的 key 列 col1 的类型为 BIGINT，并移动到 col2 列后面。
-
-```sql
-ALTER TABLE example_db.my_table
-MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
-```
-
-注意：无论是修改 key 列还是 value 列都需要声明完整的 column 信息
-
-8. 修改 base index 的 val1 列最大长度。原 val1 为 (val1 VARCHAR(32) REPLACE DEFAULT "abc")
-
-```sql
-ALTER TABLE example_db.my_table
-MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
-```
-注意：只能修改列的类型，列的其他属性维持原样
-
-9. 重新排序 example_rollup_index 中的列（设原列顺序为：k1,k2,k3,v1,v2）
-
-```sql
-ALTER TABLE example_db.my_table
-ORDER BY (k3,k1,k2,v2,v1)
-FROM example_rollup_index;
-```
-
-10. 同时执行两种操作
-
-```sql
-ALTER TABLE example_db.my_table
-ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
-ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;
-```
-
-11. 修改Duplicate key 表 Key 列的某个字段的长度
-
-```sql
-alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
-```
-
-
+**Example**
+
+1. 修改 example_db.my_table 的 key 列 col1 的类型为 BIGINT，并移动到 col2 列后面。
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
+ ```
+
+ :::note
+ 无论是修改 key 列还是 value 列都需要声明完整的 column 信息
+ :::
+
+2. 修改 example_db.my_table 的 val1 列最大长度。原 val1 为 (val1 VARCHAR(32) REPLACE
DEFAULT "abc")
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
+ ```
+
+ :::note
+ 只能修改列的类型，列的其他属性维持原样
+ :::
+
+3. 修改 Duplicate key 表 Key 列的某个字段的长度
+
+ ```sql
+ ALTER TABLE example_db.my_table
+ MODIFY COLUMN k3 VARCHAR(50) KEY NULL COMMENT 'to 50';
+ ```
+
+ :::note
+ - 聚合模型如果修改 value 列，需要指定 agg_type
+ - 非聚合类型如果修改key列，需要指定KEY关键字
+ - 只能修改列的类型，列的其他属性维持原样（即其他属性需在语句中按照原属性显式的写出，参见 example 8）
+ - 分区列和分桶列不能做任何修改
+ - 目前支持以下类型的转换（精度损失由用户保证）
+ - TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE 类型向范围更大的数字类型转换
+ - TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL 转换成 VARCHAR
+ - VARCHAR 支持修改最大长度
+ - VARCHAR/CHAR 转换成 TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
+ - VARCHAR/CHAR 转换成 DATE (目前支持"%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d",
"%Y/%m/%d, "%y/%m/%d"六种格式化格式)
+ - DATETIME 转换成 DATE(仅保留年-月-日信息, 例如: `2019-12-09 21:47:05` <-->
`2019-12-09`)
+ - DATE 转换成 DATETIME(时分秒自动补零， 例如: `2019-12-09` <--> `2019-12-09 00:00:00`)
+ - FLOAT 转换成 DOUBLE
+ - INT 转换成 DATE (如果INT类型数据不合法则转换失败，原始数据不变)
+ - 除DATE与DATETIME以外都可以转换成STRING，但是STRING不能转换任何其他类型 :::
+
+**5. 对指定表的列进行重新排序**
+
+**语法**
+
+ ```sql
+ ALTER TABLE [database.]table table_name ORDER BY (column_name1,
column_name2, ...)
+ [FROM rollup_index_name]
+ [PROPERTIES ("key"="value", ...)]
+ ```
+
+**Example**
+
+1. 调整 example_db.my_table 的 key 列 和 value 列的顺序（非聚合模型）
+
+ ```sql
+ CREATE TABLE `my_table`(
+ `k_1` INT NULL,
+ `k_2` INT NULL,
+ `v_1` INT NULL,
+ `v_2` varchar NULL,
+ `v_3` varchar NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k_1`, `k_2`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ALTER TABLE example_db.my_table ORDER BY (k_2,k_1,v_3,v_2,v_1);
+
+ mysql> desc my_table;
+ +-------+------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+------------+------+-------+---------+-------+
+ | k_2 | INT | Yes | true | NULL | |
+ | k_1 | INT | Yes | true | NULL | |
+ | v_3 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_2 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_1 | INT | Yes | false | NULL | NONE |
+ +-------+------------+------+-------+---------+-------+
+ ```
+
+2. 同时执行添加列和列排序操作
+
+ ```sql
+ CREATE TABLE `my_table` (
+ `k_1` INT NULL,
+ `k_2` INT NULL,
+ `v_1` INT NULL,
+ `v_2` varchar NULL,
+ `v_3` varchar NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k_1`, `k_2`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ALTER TABLE example_db.my_table
+ ADD COLUMN col INT DEFAULT "0" AFTER v_1,
+ ORDER BY (k_2,k_1,v_3,v_2,v_1,col);
+
+ mysql> desc my_table;
+ +-------+------------+------+-------+---------+-------+
+ | Field | Type | Null | Key | Default | Extra |
+ +-------+------------+------+-------+---------+-------+
+ | k_2 | INT | Yes | true | NULL | |
+ | k_1 | INT | Yes | true | NULL | |
+ | v_3 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_2 | VARCHAR(*) | Yes | false | NULL | NONE |
+ | v_1 | INT | Yes | false | NULL | NONE |
+ | col | INT | Yes | false | 0 | NONE |
+ +-------+------------+------+-------+---------+-------+
+ ```
+
+:::note
+ - index 中的所有列都要写出来
+ - value 列在 key 列之后
+ - key 列只能调整 key 列的范围内进行调整，value 列同理
+:::
### Keywords
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
