ALTER TABLE

Syntax description

ALTER TABLE is used to modify the existing data table structure.

Syntax structure

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {[INDEX | KEY] [index_name]
        [index_option] ...
  | ADD [CONSTRAINT] UNIQUE [INDEX | KEY]
        [index_name][index_option] ...
  | ADD [CONSTRAINT] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DROP PRIMARY KEY
  | RENAME [TO | AS] new_tbl_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | RENAME COLUMN old_col_name TO new_col_name
    }

key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
  COMMENT[=]'string'
}
table_options:
    table_option [[,] table_option] ...
table_option: {
  COMMENT [=] 'string'
}

Syntax Explanation

Below are the explanations of each parameter:

ALTER TABLE tbl_name: means modifying the table named tbl_name.
alter_option: means one or more change options can be executed, separated by commas.
table_options: used to set or modify table options, such as table comments (COMMENT).
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]: adds a new column to the table, and you can specify the position where the new column is inserted (before or after a certain column).
ADD [COLUMN] (col_name column_definition,...): adds multiple new columns at the same time.
ADD {[INDEX | KEY] [index_name] [index_option] ...: adds an index, and you can specify the index name and index options (for example, comments).
ADD [CONSTRAINT] UNIQUE [INDEX | KEY] [index_name][index_option] ...: Add a UNIQUE constraint or UNIQUE index.
ADD [CONSTRAINT] FOREIGN KEY [index_name] (col_name,...) reference_definition: Add a FOREIGN KEY constraint.
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...): Add a primary key constraint.
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]: Modify column definition, column name and order.
ALTER INDEX index_name {VISIBLE | INVISIBLE}: Change the visibility of an index.
DROP [COLUMN] col_name: Drop a column.
DROP {INDEX | KEY} index_name: Delete an index.
DROP FOREIGN KEY fk_symbol: Delete a FOREIGN KEY constraint.
DROP PRIMARY KEY: Delete a primary key.
RENAME [TO | AS] new_tbl_name: Rename the entire table.
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]: Modify the column definition and order.
RENAME COLUMN old_col_name TO new_col_name: Rename a column.
key_part: Indicates the index component, you can use the column name (when creating an index on a text column, you can specify a length for the index, the character length is variable. If you use the column name and do not specify the length when creating the index, the index will use the value of the entire column as the index component. This may cause performance degradation in some cases, especially when processing large text columns or binary data columns. For smaller data types, such as integers or dates, it is usually not necessary to specify the length).
index_option: Indicates index options, such as comments (COMMENT).
table_options: Indicates table options, such as table comments (COMMENT).
table_option: Specific table options, such as comments (COMMENT).

Example

Example 1:

-- Create table f1 with two integer columns: fa (primary key) and fb (key with unique constraint)
CREATE TABLE f1(fa INT PRIMARY KEY, fb INT UNIQUE KEY);
-- Create table c1 with two integer columns: ca and cb
CREATE TABLE c1 (ca INT, cb INT);
-- Add a foreign key constraint named ffa to table c1, associating the ca column of table c1 with the fa column of table f1
ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
-- Insert a record into table f1: (2, 2)
INSERT INTO f1 VALUES (2, 2);
-- Insert a record into table c1: (1, 1)
INSERT INTO c1 VALUES (1, 1);
-- Add a foreign key constraint into table c1: ffa Insert a record into the table: (2, 2)
INSERT INTO c1 VALUES (2, 2);
-- Select all records from the c1 table and sort by the ca column
mysql> select ca, cb from c1 order by ca;
+------+------+
| ca | cb |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.01 sec)
-- Delete the foreign key constraint named ffa from the c1 table
ALTER TABLE c1 DROP FOREIGN KEY ffa;
-- Insert a record into the c1 table: (1, 1)
INSERT INTO c1 VALUES (1, 1);
-- Select all records from the c1 table and sort by the ca columnmysql> select ca, cb from c1 order by ca;
+------+------+
| ca   | cb   |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.01 sec)

Example 2:

-- Create a new table named 't1' with columns a, b, c, and d. Column 'a' is of INTEGER data type, 'b' is of CHAR(10), 'c' is of DATE data type, and 'd' is of DECIMAL(7,2). A unique key is added on columns 'a' and 'b'.
CREATE TABLE t1(a INTEGER, b CHAR(10), c DATE, d DECIMAL(7,2), UNIQUE KEY(a, b));

-- View the structure of table 't1'.
mysql> desc t1;
+-------+--------------+------+------+---------+-------+---------+
| Field | Type | Null | Key | Default | Extra | Comment |
+-------+--------------+------+------+---------+----------+---------+
| a | INT(32) | YES | | NULL | | |
| b | CHAR(10) | YES | | NULL | | |
| c | DATE(0) | YES | | NULL | | |
| d | DECIMAL64(7) | YES | | NULL | | |
+-------+--------------+------+------+---------+-------+---------+
4 rows in set (0.01 sec)

-- Insert three rows into table 't1'.
INSERT INTO t1 VALUES(1, 'ab', '1980-12-17', 800);
INSERT INTO t1 VALUES(2, 'ac', '1981-02-20', 1600);
INSERT INTO t1 VALUES(3, 'ad', '1981-02-22', 500);

-- Display all rows in table 't1'.
mysql> select * from t1;+------+------------------------------------+
| a | b | c | d |
+------+----------------------------------------+
| 1 | ab | 1980-12-17 | 800.00 |
| 2 | ac | 1981-02-20 | 1600.00 |
| 3 | ad | 1981-02-22 | 500.00 |
+------+----------------------------------------+
3 rows in set (0.01 sec)

-- Modify table 't1' and add the primary key 'pk1' on columns 'a' and 'b'.
mysql> alter table t1 add primary key pk1(a, b);
Query OK, 0 rows affected (0.02 sec)

-- Check again the structure of the modified table 't1'.
mysql> desc t1;
+------------------------+----------------------------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra | Comment |
+------------------------+----------------------------------------------------------------------------------------------------------------
| a | INT(32) | NO | PRI | null | | |
| b | CHAR(10) | NO | PRI | null | | |
| c | DATE(0) | YES | | null | | | |
| d | DECIMAL64(7) | YES | | null | | | |
+------------------------+----------------------------------------------------------------------------------------------------------------
4 rows in set (0.01 sec)

-- After adding the primary key, display all rows in table 't1' again.
mysql> select * from t1;
+------+----------------------------------------+
| a | b | c | d |
+------+----------------------------------------+
| 1 | ab | 1980-12-17 | 800.00 |
| 2 | ac | 1981-02-20 | 1600.00 |
| 3 | ad | 1981-02-22 | 500.00 |
+------+----------------------------------------+
3 rows in set (0.00 sec)

Example 3:

CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10));
mysql> desc t1;
+--------+--------+------+--------+----------+-------------+
| Field | Type | Null | Key | Default | Extra | Comment |
+--------+--------+------+--------+----------+-------------+
| a | INT(32) | NO | PRI | NULL | | | |
| b | CHAR(10) | YES | | NULL | | | |
+--------+--------+------+--------+----------+-------------+
2 rows in set (0.01 sec)

insert into t1 values(1, 'ab');
insert into t1 values(2, 'ac');
insert into t1 values(3, 'ad');

mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | ab |
| 2 | ac |
| 3 | ad |
+------+------+
3 rows in set (0.01 sec)

-- Modify table 't1', change the name of column 'a' to 'x', and change the data type to VARCHAR(20).
mysql> alter table t1 change a x VARCHAR(20);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-----------------------+---------+--------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra | Comment |
+-----------------------+---------+--------------------------------------------------------------------------------------------
| x | VARCHAR(20) | NO | PRI | null | | |
| b | CHAR(10) | YES | | null | | | |
+-----------------------+---------+--------------------------------------------------------------------------------------------
2 rows in set (0.01 sec)

mysql> select * from t1;
+------+------+
| x | b |
+------+------+
| 1 | ab |
| 2 | ac |
| 3 | ad |
+------+------+
3 rows in set (0.00 sec)

limit

These clauses: CHANGE [COLUMN], MODIFY [COLUMN], RENAME COLUMN, ADD [CONSTRAINT [symbol]] PRIMARY KEY, DROP PRIMARY KEY and ALTER COLUMN ORDER BY can be used freely in combination in the ALTER TABLE statement, but are not supported for the time being.
Temporary tables do not support the use of ALTER TABLE to modify the table structure.
Use CREATE TABLE ... CLUSTER BY... to modify the table structure using ALTER TABLE.