Syntax
------

ALTER [ONLINE] [IGNORE] TABLE [IF EXISTS] tbl_name
  [WAIT n | NOWAIT]
  alter_specification [, alter_specification] ...
alter_specification:
  table_option ...
 | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
    [FIRST | AFTER col_name ]
 | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition,...)
 | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
    [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]] PRIMARY KEY
    [index_type] (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
    UNIQUE [INDEX|KEY] [index_name]
    [index_type] (index_col_name,...) [index_option] ...
 | ADD FULLTEXT [INDEX|KEY] [index_name]
    (index_col_name,...) [index_option] ...
 | ADD SPATIAL [INDEX|KEY] [index_name]
    (index_col_name,...) [index_option] ...
 | ADD [CONSTRAINT [symbol]]
    FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
    reference_definition
 | ADD PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
 | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
 | ALTER [COLUMN] col_name DROP DEFAULT
 | ALTER {INDEX|KEY} index_name [NOT] INVISIBLE
 | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition
    [FIRST|AFTER col_name]
 | MODIFY [COLUMN] [IF EXISTS] col_name column_definition
    [FIRST | AFTER col_name]
 | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
 | DROP PRIMARY KEY
 | DROP {INDEX|KEY} [IF EXISTS] index_name
 | DROP FOREIGN KEY [IF EXISTS] fk_symbol
 | DROP CONSTRAINT [IF EXISTS] constraint_name
 | DISABLE KEYS
 | ENABLE KEYS
 | RENAME [TO] new_tbl_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
 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
 | [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name
 | DISCARD TABLESPACE
 | IMPORT TABLESPACE
 | ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
 | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
 | FORCE
 | partition_options
 | ADD PARTITION [IF NOT EXISTS] (partition_definition)
 | DROP PARTITION [IF EXISTS] partition_names
 | COALESCE PARTITION number
 | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
 | ANALYZE PARTITION partition_names
 | CHECK PARTITION partition_names
 | OPTIMIZE PARTITION partition_names
 | REBUILD PARTITION partition_names
 | REPAIR PARTITION partition_names
 | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
 | REMOVE PARTITIONING
 | ADD SYSTEM VERSIONING
 | DROP SYSTEM VERSIONING
index_col_name:
  col_name [(length)] [ASC | DESC]
index_type:
  USING {BTREE | HASH | RTREE}
index_option:
  [ KEY_BLOCK_SIZE [=] value
 | index_type
 | WITH PARSER parser_name
 | COMMENT 'string'
 | CLUSTERING={YES| NO} ]
 [ IGNORED | NOT IGNORED ]
table_options:
  table_option [[,] table_option] ...

Description
-----------

ALTER TABLE enables you to change the structure of an existing 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 the comment for the table and the storage engine of the table.

If another connection is using the table, a metadata lock is active, and this
statement will wait until the lock is released. This is also true for
non-transactional tables.

When adding a UNIQUE index on a column (or a set of columns) which have
duplicated values, an error will be produced and the statement will be
stopped. To suppress the error and force the creation of UNIQUE indexes,
discarding duplicates, the IGNORE option can be specified. This can be useful
if a column (or a set of columns) should be UNIQUE but it contains duplicate
values; however, this technique provides no control on which rows are
preserved and which are deleted. Also, note that IGNORE is accepted but
ignored in ALTER TABLE ... EXCHANGE PARTITION statements.

This statement can also be used to rename a table. For details see RENAME
TABLE.

When an index is created, the storage engine may use a configurable buffer in
the process. Incrementing the buffer speeds up the index creation. Aria and
MyISAM allocate a buffer whose size is defined by aria_sort_buffer_size or
myisam_sort_buffer_size, also used for REPAIR TABLE. InnoDB allocates three
buffers whose size is defined by innodb_sort_buffer_size.

Privileges
----------

Executing the ALTER TABLE statement generally requires at least the ALTER
privilege for the table or the database..

If you are renaming a table, then it also requires the DROP, CREATE and INSERT
privileges for the table or the database as well.

Online DDL
----------

Online DDL is supported with the ALGORITHM and LOCK clauses.

See InnoDB Online DDL Overview for more information on online DDL with InnoDB.

ALTER ONLINE TABLE
------------------

ALTER ONLINE TABLE also works for partitioned tables.

Online ALTER TABLE is available by executing the following:

ALTER ONLINE TABLE ...;

This statement has the following semantics:

This statement is equivalent to the following:

ALTER TABLE ... LOCK=NONE;

See the LOCK alter specification for more information.

This statement is equivalent to the following:

ALTER TABLE ... ALGORITHM=INPLACE;

See the ALGORITHM alter specification for more information.

WAIT/NOWAIT
-----------

MariaDB starting with 10.3.0
----------------------------