Syntax:
ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK|CONSTRAINT} symbol
  | ALTER {CHECK|CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
  | ALTER [COLUMN] col_name
        {SET DEFAULT {literal | (expr)} | DROP DEFAULT}
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE|ENABLE} KEYS
  | {DISCARD|IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX|KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | RENAME [TO|AS] new_tbl_name
  | {WITHOUT|WITH} VALIDATION

partition_options:
    partition_option [partition_option] ...

partition_option:
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

table_options:
    table_option [[,] table_option] ...

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table or
the table comment.

o To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for
  the table. Renaming a table requires ALTER and DROP on the old table,
  ALTER, CREATE, and INSERT on the new table.

o Following the table name, specify the alterations to be made. If none
  are given, ALTER TABLE does nothing.

o The syntax for many of the permissible alterations is similar to
  clauses of the CREATE TABLE statement. column_definition clauses use
  the same syntax for ADD and CHANGE as for CREATE TABLE. For more
  information, see [HELP CREATE TABLE].

o The word COLUMN is optional and can be omitted, except for RENAME
  COLUMN (to distinguish a column-renaming operation from the RENAME
  table-renaming operation).

o Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a
  single ALTER TABLE statement, separated by commas. This is a MySQL
  extension to standard SQL, which permits only one of each clause per
  ALTER TABLE statement. For example, to drop multiple columns in a
  single statement, do this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

o If a storage engine does not support an attempted ALTER TABLE
  operation, a warning may result. Such warnings can be displayed with
  SHOW WARNINGS. See [HELP SHOW WARNINGS]. For information on
  troubleshooting ALTER TABLE, see
  https://dev.mysql.com/doc/refman/8.0/en/alter-table-problems.html.

o For information about generated columns, see
  https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns
  .html.

o For usage examples, see
  https://dev.mysql.com/doc/refman/8.0/en/alter-table-examples.html.

o InnoDB in MySQL 8.0.17 and later supports addition of multi-valued
  indexes on JSON columns using a key_part specification can take the
  form (CAST json_path AS type ARRAY). See
  https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-inde
  x-multi-valued, for detailed information regarding multi-valued index
  creation and usage of, as well as restrictions and limitations on
  multi-valued indexes.

o With the mysql_info() C API function, you can find out how many rows
  were copied by ALTER TABLE. See
  https://dev.mysql.com/doc/refman/8.0/en/mysql-info.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

