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 ...

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.