MENU


ALTER TABLE

ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]     [partition_options]
algorithm_option:     ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
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
| 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
| 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
index_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)
Setting ALGORITHM to COPY causes a temporary copy of the original table to be made during the alteration. MySQL waits for modifications on the table, alters the copy, deletes the original table and renames the new one. Updates and writes after the ALTER TABLE operation are stalled until the new table is ready.

Setting ALGORITHM to INPLACE uses the in-place technique for clauses and storage engines that support it, and fails otherwise. Setting ALGORITHM to DEFAULT is the same as specifying no ALGORITHM at all.
Setting LOCK to DEFAULT results in maximum concurrency. Setting it to NONE permits concurrent reads and writes if supported, and returns an error message otherwise. Setting it to SHARED permits concurrent reads but block writes, and returns an error message if concurrent reads are not supported. Setting it to EXCLUSIVE blocks reads and writes.
If IGNORE is specified, when duplicates are found for unique keys, only one row is used on a unique key, the other conflicting rows are removed, and incorrect values are truncated to the closest matching acceptable value. If IGNORE is not specified, the copy is aborted and rolled back.
ALTER TABLE cannot be used to change the storage engine of a table to MERGE or BLACKHOLE, to prevent loss of data.
ORDER BY allows you to create the new table with the rows in a particular order after the ALTER TABLE operation.