ALTER-TABLE-COLUMN

Name

ALTER TABLE COLUMN

Description

This statement is used to perform a schema change operation on an existing table. The schema change is asynchronous and will be returned if the task is completed. After that, you can use the SHOW ALTER command to view the progress.

Syntax:

ALTER TABLE [database.]table alter_clause;

The alter_clause of schema change supports the following modifications:

1. Add a column to the specified position at the specified index

Syntax:

ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

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 may not 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

Syntax:

ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

Note:

• If you want to add a value column to the aggregation model, you need to specify agg_type
• If you want to 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

Syntax:

DROP COLUMN column_name
[FROM rollup_index_name]

Note:

• You may not delete a partition column
• If the column is removed from the base index and it is included in the rollup index, it will also be removed.

4. Modify the column type and column position of the specified index

Syntax:

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", ...)]

Note:

• If you want to modify the value column in the aggregation model, you need to specify agg_type
• If you want to 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

Syntax:

ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

Note:

• 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)

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)

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

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

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)

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

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.

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")

ALTER TABLE example_db.my_table
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

9. Reorder the columns in example_rollup_index (set the original column order as: k1,k2,k3,v1,v2)

ALTER TABLE example_db.my_table
ORDER BY (k3,k1,k2,v2,v1)
FROM example_rollup_index;

10. Perform two operations at a time

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

alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'

Keywords

ALTER, TABLE, COLUMN, ALTER TABLE

Best Practice