How to ADD COLUMN in MySQL

Why use ALTER TABLE ADD COLUMN?

Add new data attributes to live tables without downtime, letting applications evolve as requirements grow.

How do I add a simple column?

Run ALTER TABLE Products ADD COLUMN sku VARCHAR(40); to store product SKUs. MySQL appends the column after the last existing one by default.

How can I control the column position?

Use FIRST to push it to the front or AFTER existing_column to insert it mid-table. Example: ALTER TABLE Orders ADD COLUMN currency CHAR(3) AFTER total_amount;

How do I set a default value?

Include DEFAULT. Example: ALTER TABLE Customers ADD COLUMN status ENUM('active','inactive') NOT NULL DEFAULT 'active'; ensures new customers start as active.

Can I add multiple columns at once?

Yes. Separate definitions with commas: ALTER TABLE OrderItems ADD COLUMN list_price DECIMAL(10,2), ADD COLUMN discount DECIMAL(5,2) DEFAULT 0.00;

What about generated columns?

Computed values can be added with GENERATED ALWAYS AS. Example: ALTER TABLE OrderItems ADD COLUMN subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * list_price);

How do I avoid locking large tables?

Use ALGORITHM=INPLACE, LOCK=NONE when possible: ALTER TABLE Products ALGORITHM=INPLACE, LOCK=NONE ADD COLUMN weight DECIMAL(6,2); This keeps reads and writes available.

Best practices for production deployments?

1) Add nullable columns first, back-fill data, then apply NOT NULL.
2) Perform in low-traffic windows.
3) Always test on staging with similar data volume.

Common errors and fixes

ER_DUP_FIELDNAME appears when the column already exists—double-check with DESCRIBE table_name. ER_BAD_FIELD_ERROR happens when AFTER refers to a non-existent column—verify spelling.