13.1.7 ALTER TABLE Syntax ALTER TABLE Partition Operations ALTER TABLE Online Operations in MySQL Cluster ALTER TABLE Examples
    [alter_specification [, alter_specification] ...]

  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
        [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 [index_name] (index_col_name,...)
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | 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}

    col_name [(length)] [ASC | DESC]


    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

    table_option [[,] table_option] ...  (see CREATE TABLE 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.

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

The syntax for many of the permissible alterations is similar to clauses of the CREATE TABLE statement. See Section 13.1.17, “CREATE TABLE Syntax”, for more information.

table_options signifies table options of the kind that can be used in the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, MAX_ROWS, or ROW_FORMAT. For a list of all table options and a description of each, see Section 13.1.17, “CREATE TABLE Syntax”. However, ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options.

partition_options signifies options that can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. It is possible for an ALTER TABLE statement to contain a PARTITION BY or REMOVE PARTITIONING clause in an addition to other alter specifications, but the PARTITION BY or REMOVE PARTITIONING clause must be specified last after any other specifications. The ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE PARTITION, CHECK PARTITION, and REPAIR PARTITION options cannot be combined with other alter specifications in a single ALTER TABLE, since the options just listed act on individual partitions. For more information about partition options, see Section 13.1.17, “CREATE TABLE Syntax”, and Section, “ALTER TABLE Partition Operations”. For information about and examples of ALTER TABLE ... EXCHANGE PARTITION statements, see Section 19.3.3, “Exchanging Partitions and Subpartitions with Tables”.

Some operations may result in warnings if attempted on a table for which the storage engine does not support the operation. These warnings can be displayed with SHOW WARNINGS. See Section, “SHOW WARNINGS Syntax”.

For information on troubleshooting ALTER TABLE, see Section B.5.6.1, “Problems with ALTER TABLE”.

Storage, Performance, and Concurrency Considerations

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes).

For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:

You can force an ALTER TABLE operation that would otherwise not require a table copy to use the temporary table method (as supported in MySQL 5.0) by setting the old_alter_table system variable to ON, or specifying ALGORITHM=COPY as one of the alter_specification clauses. If there is a conflict between the old_alter_table setting and an ALGORITHM clause with a value other than DEFAULT, the ALGORITHM clause takes precedence. (ALGORITHM = DEFAULT is the same a specifying no ALGORITHM clause at all.)

Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Section 14.10, “InnoDB and Online DDL” for information about online DDL for InnoDB tables.

As of MySQL 5.6.16, ALTER TABLE upgrades MySQL 5.5 temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

Starting with MySQL 5.6.22, an ALTER TABLE operation on a multicolumn index used to partition a table by KEY cannot be performed online when the operation would change the order of the columns. In such cases, you must use a copying ALTER TABLE instead. (Bug #17896265)

MySQL Cluster also supports online ALTER TABLE operations using the ALGORITHM=INPLACE syntax in MySQL Cluster NDB 7.3 and later. MySQL Cluster also supports an older syntax specific to NDB that uses the ONLINE and OFFLINE keywords. These keywords are deprecated beginning with MySQL Cluster NDB 7.3; they continue to be supported in MySQL Cluster NDB 7.4, but are subject to removal in a future version of MySQL Cluster. See Section, “ALTER TABLE Online Operations in MySQL Cluster”, for the exact syntax and other particulars.

You can control the level of concurrent reading and writing of the table while it is being altered, using the LOCK clause. Specifying a non-default value for this clause lets you require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available. The parameters for the LOCK clause are:

As of MySQL 5.6.3, you can also use ALTER TABLE tbl_name FORCE to perform a null alter operation that rebuilds the table. Previously the FORCE option was recognized but ignored. As of MySQL 5.6.17, online DDL support is provided for the FORCE option. For more information, see Section 14.10.1, “Overview of Online DDL”.

For NDB tables, operations that add and drop indexes on variable-width columns occur online, without any table copying and without blocking concurrent DML actions for most of their duration. See Section, “ALTER TABLE Online Operations in MySQL Cluster”.

Usage Notes

With the mysql_info() C API function, you can find out how many rows were copied by ALTER TABLE, and (when IGNORE is used) how many rows were deleted due to duplication of unique key values. See Section, “mysql_info()”.