how to add and delete columns in an existing table?

Q: how to add and delete columns in an existing table?

ALTER TABLE table_name ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column], ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column], ALTER TABLE CUSTOMERS ADD COLUMN AGE INT NOT NULL; ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE CUSTOMERS DROP COLUMN AGE, DROP COLUMN GENDER;

Difficulty: 6/10

Online DDL, Schema Migrations, Table Locking

We can add one or multiple columns in a table using the ALTER TABLE ADD statement.

Syntax:

Dropping column:

Scenario Questions

0-2 years experience

1We have a 'users' table and need to add a new 'middle_name' column right after 'first_name'. How would you write the SQL for this, and how do we ensure existing users get a default value of NULL?
2Imagine you need to drop a 'phone_number' column from our production database because of a privacy policy change. What checks would you perform on the application code before running that ALTER TABLE statement to prevent runtime errors?

2-5 years experience

Follow-up Questions

How does MySQL handle default values internally when adding a column instantly?
What happens to the physical disk space when you drop a column in InnoDB?
How do you monitor replication lag during a heavy ALTER TABLE operation?