O'Reilly logo

MySQL in a Nutshell by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

ALTER TABLE

Synopsis

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

Use this statement to change an existing table's structure and other properties. Alterations to a table can include adding a new column (see the ADD clauses that follow), changing an existing column (see the ALTER, CHANGE, and MODIFY clauses), deleting a column or index (see the DROP clauses), and miscellaneous other tablewide settings. 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 rolled back.

The following are the syntax and explanations of each clause, with examples, grouped by type of clause. Multiple alterations may be combined in a single ALTER TABLE statement. They must be separated by commas and each clause must include the minimally required elements.

ADD clauses for columns and indexes

ALTER [IGNORE] TABLE table
ADD [COLUMN] column definition [FIRST|AFTER column]
ADD INDEX [index] [USING type] (column,...)
ADD [FULLTEXT|SPATIAL] [index] (column,...)

These clauses add columns and indexes to a table. The first syntax adds a new column to a table. The same column definition that would be used in a CREATETABLE statement is used in this syntax and in several others found with the ALTER TABLE statement. Basically, the name of the column is given, followed by the column datatype ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required