MySQueaL Resources

ALTER TABLE Statement

After you've created a table, use this MySQL statement to change the structure of a table.

Use this MySQL statement to change an existing MySQL table's structure and other properties. A MySQL table may be altered by several ways with this statement:

• Adding a new column (see the ADD clauses for columns documentation page);
• Adding an index (see the three documentation pages: ADD clause for standard indexes, ADD clause for FULLTEXT indexes, and ADD clause for SPATIAL indexes);
• Adding a foreign key constraint (see ADD clauses for foreign keys);
• Changing an existing column (see the CHANGE clauses page);
• Deleting a column or index (see the DROP column clause and DROP index clauses pages);
• Setting other column and index factors (see the Miscellaneous clauses documentation page);
• Adding and changing table partitions (see the Partition altering clauses and Partition administration clauses); and
• Setting table wide options (see the Table options page).

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 MySQL statement requires the ALTER, CREATE, and INSERT privileges for the table being altered, at a minimum. While an ALTER TABLE statement is being executed, users will be able to read the table, but usually they won't be able to modify data or add data to a table being altered. Any INSERT statements using the DELAYED parameter that are not completed when a table is altered will be canceled and the data lost. Increasing the size of the myisam_sort_buffer_size system variable will sometimes make MyISAM table alterations go faster.

There are several methods to alter a table with this MySQL statement and our MySQL documentation gives several examples: see the links in the right margin list other MySQL resources. Still, below is a generic example of how this statement is used:

ALTER TABLE sales_prospects
CHANGE COLUMN city city VARCHAR(255),
DROP COLUMN assistant_name;

As you can see, multiple changes may be made to a MySQL table in one execution of the ALTER TABLE statement—they just need to be separated by commas.