ALTER-TABLE-COLUMN

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 the task is returned when the task is submitted successfully. After that, you can use the SHOW ALTER TABLE COLUMN command to view the progress.

grammar:

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

grammar:

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

Notice:

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

2. Add multiple columns to the specified index

grammar:

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:

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:

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:

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)

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. Do Two Actions Simultaneously

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