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
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | 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}
  | PARTITION BY partitioning_expression

    col_name [(length)] [ASC | DESC]


    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

    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. A number of partitioning-related extensions to ALTER TABLE were added in MySQL 5.1.5. These 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, 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 a list of partition options and a description of each, see Section 13.1.17, “CREATE TABLE Syntax”. For additional information, see Section, “ALTER TABLE Partition Operations”.

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. 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.

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.

For NDBCLUSTER 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()”.