MySQL Alter Table

ALTER TABLE modifies an existing table's structure, letting you add, drop, or change columns without recreating the table.

What ALTER TABLE Does

As applications evolve, database schemas need to evolve with them. ALTER TABLE is the statement that changes a table's structure after it has already been created — adding new columns, removing obsolete ones, renaming them, or changing their data types — all while preserving existing data wherever possible.

Adding a Column

ADD COLUMN inserts a new column into an existing table. You can optionally specify where it goes using FIRST or AFTER an existing column; if omitted, MySQL appends the new column to the end of the table.

Add a new column

ALTER TABLE customers
ADD COLUMN loyalty_points INT DEFAULT 0;

Dropping a Column

DROP COLUMN removes a column and all of its data permanently. This should be done cautiously, especially in production, since any application code or view referencing the column will break immediately after the change.

Remove a column

ALTER TABLE customers
DROP COLUMN fax_number;

Modifying a Column

MODIFY COLUMN changes a column's data type, size, or constraints while keeping its name the same. If you also need to rename the column, use CHANGE COLUMN instead, which requires specifying both the old and new column definitions.

Change a column's data type

ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2) NOT NULL;
  • ADD COLUMN — inserts a new column, optionally positioned with FIRST or AFTER
  • DROP COLUMN — permanently deletes a column and its data
  • MODIFY COLUMN — changes type/constraints, keeps the same name
  • CHANGE COLUMN — renames a column and can change its type at the same time
  • RENAME TO — renames the entire table
ClausePurposeExample
ADD COLUMNIntroduce a new fieldADD COLUMN email VARCHAR(255)
DROP COLUMNRemove a field entirelyDROP COLUMN legacy_id
MODIFY COLUMNChange type/constraint, same nameMODIFY COLUMN age SMALLINT
CHANGE COLUMNRename and/or retype a fieldCHANGE COLUMN nm name VARCHAR(50)
RENAME TORename the whole tableRENAME TO clients
Note: On very large tables, ALTER TABLE can lock the table or rebuild it entirely depending on the storage engine and the type of change, which may cause noticeable downtime. Test schema changes on a staging copy first, and consider tools like pt-online-schema-change for large production tables.
Note: You can combine multiple changes in a single ALTER TABLE statement by separating clauses with commas, which MySQL applies as one atomic operation rather than running several separate statements.

Exercise: MySQL Alter Table

Which clause adds a new column to an existing table?

Keep Going

MySQL Interview QuestionsPractice real questions asked for MySQLDatabase Administrator SalarySee what Database Administrator roles pay in the marketAuto Apply for JobsApply to open roles related to MySQL and tweak your resume