MySQL in a Nutshell, 2nd Edition

Name

ALTER TABLE

Synopsis

ALTER [IGNORE] TABLE table changes[, ...]

Use this statement to change an existing table’s structure and other properties. A table may be altered with this statement in the following ways:

• Add a new column (see the ALTER TABLE: ADD clauses for columns” subsection that follows)

• Add an index (see the ALTER TABLE: ADD clause for standard indexes,” ALTER TABLE: ADD clause for FULLTEXT indexes,” and ALTER TABLE: ADD clause for SPATIAL indexes” subsections)

• Add a foreign key constraint (see the ALTER TABLE: ADD clauses for foreign keys” subsection)

• Change an existing column (see the ALTER TABLE: CHANGE clauses” subsection)

• Delete a column or index (see the ALTER TABLE: DROP column clause” and ALTER TABLE: DROP index clauses” subsections)

• Set other column and index factors (see the ALTER TABLE: Miscellaneous clauses” subsection)

• Add and change table partitions (see the ALTER TABLE: Partition altering clauses” and ALTER TABLE: Partition administration clauses” subsections)

• Set table-wide options (see the ALTER TABLE: Table options” subsection)

The IGNORE flag applies to all clauses and instructs MySQL to ignore any error messages regarding duplicate rows that may occur as a result of a column change. It will keep the first unique row found and drop any duplicate rows. Otherwise, the statement will be terminated and changes will be rolled back.

This statement requires the ALTER, CREATE, and INSERT privileges for the table being altered, at a minimum. While an ALTER TABLE statement ...