13.1.6. ALTER TABLE Syntax ALTER TABLE Partition Operations ALTER TABLE Examples
ALTER [IGNORE] 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] (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 [index_name] (index_col_name,...)        reference_definition  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  | CHANGE [COLUMN] old_col_name new_col_name column_definition        [FIRST|AFTER col_name]  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}  | MODIFY [COLUMN] col_name column_definition        [FIRST | AFTER col_name]  | DROP [COLUMN] col_name  | DROP PRIMARY KEY  | DROP {INDEX|KEY} index_name  | DROP FOREIGN KEY fk_symbol  | DISABLE KEYS  | ENABLE KEYS  | RENAME [TO|AS] new_tbl_name  | RENAME {INDEX|KEY} old_index_name TO new_index_name  | ORDER BY col_name [, col_name] ...  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]  | DISCARD TABLESPACE  | IMPORT TABLESPACE  | FORCE  | ADD PARTITION (partition_definition)  | DROP PARTITION partition_names  | TRUNCATE PARTITION {partition_names | ALL}  | COALESCE PARTITION number  | 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}  | REMOVE PARTITIONINGindex_col_name:    col_name [(length)] [ASC | DESC]index_type:    USING {BTREE | HASH}index_option:    KEY_BLOCK_SIZE [=] value  | index_type  | WITH PARSER parser_name  | COMMENT 'string'table_options:    table_option [[,] table_option] ...  (see CREATE TABLE options)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.

Partitioning-related clauses for ALTER TABLE 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 more information, see Section, "ALTER TABLE Partition Operations".

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.14, "CREATE TABLE Syntax", for more information.

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 C.5.7.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 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 5.5, "Online DDL for InnoDB Tables" for information about online DDL for InnoDB tables.

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:

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.

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()".

Spec-Zone.ru - all specs in one place