ALTER TABLE

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

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

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:

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.

Examples

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.