00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Alter Table - Add Column

Last updated: 6 min read

Requirements change: the customers table that once needed only a name and a city now needs an email, a phone number and a sign-up date. ALTER TABLE … ADD COLUMN adds new columns to an existing table without losing any data.

1. Syntax

syntax.sql 
ALTER TABLE table_name
  ADD [COLUMN] column_name data_type [constraints] [FIRST | AFTER existing_column];
  • The word COLUMN is optional.
  • Without FIRST/AFTER, the column goes at the end.
  • Existing rows receive the column's DEFAULT, or NULL if there is none.
Adding a column to an existing tableThe customers table has id, name and city. ALTER TABLE ADD COLUMN email AFTER name inserts the new column between name and city. Existing rows get the default value or NULL in the new column.Beforeidnamecity1IshaanPune2FatimaDelhiADD emailAFTER nameAfteridnameemailcity1IshaanNULLPune2FatimaNULLDelhiexisting rows get NULL (or the DEFAULT you give)
Figure 1: ADD COLUMN … AFTER places the new column exactly where you want it.

2. Adding a Column

add.sql 
ALTER TABLE customers ADD COLUMN email VARCHAR(100);
SELECT * FROM customers;

Result: 3 rows

idnamecityemail
1IshaanPuneNULL
2FatimaDelhiNULL
3GeorgeChennaiNULL

3. Choosing the Position: FIRST and AFTER

position.sql 
ALTER TABLE customers ADD COLUMN phone VARCHAR(15) AFTER name;
ALTER TABLE customers ADD COLUMN customer_code CHAR(6) FIRST;
DESCRIBE customers;

Result: 6 rows

FieldTypeNullDefault
customer_codechar(6)YESNULL
idintNONULL
namevarchar(40)NONULL
phonevarchar(15)YESNULL
cityvarchar(30)YESNULL
emailvarchar(100)YESNULL
Column order only affects SELECT * and DESCRIBE. Queries that name their columns don't care about position.

4. Adding a NOT NULL Column

A NOT NULL column needs a value for the rows that already exist. If you give no default, MySQL uses the type's implicit default (0, empty string, or a zero date), which is rarely what you want:

implicit.sql 
ALTER TABLE customers ADD COLUMN loyalty_points INT NOT NULL;
SELECT name, loyalty_points FROM customers;

Result: 3 rows

nameloyalty_points
Ishaan0
Fatima0
George0

Give an explicit default so the meaning is clear:

explicit-default.sql 
ALTER TABLE customers
  ADD COLUMN status VARCHAR(10) NOT NULL DEFAULT 'active',
  ADD COLUMN joined_on DATE NOT NULL DEFAULT (CURRENT_DATE);
SELECT name, status, joined_on IS NOT NULL AS has_join_date FROM customers;

Result: 3 rows

namestatushas_join_date
Ishaanactive1
Fatimaactive1
Georgeactive1

5. Adding Several Columns at Once

Separate several ADD clauses with commas. MySQL changes the table once, which is faster than separate statements:

multiple.sql 
ALTER TABLE customers
  ADD COLUMN birth_date DATE AFTER phone,
  ADD COLUMN newsletter BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN notes TEXT;
SELECT column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'crm' AND table_name = 'customers'
ORDER BY ordinal_position;

Result: 12 rows

COLUMN_NAMECOLUMN_TYPE
customer_codechar(6)
idint
namevarchar(40)
phonevarchar(15)
birth_datedate
cityvarchar(30)
emailvarchar(100)
loyalty_pointsint
statusvarchar(10)
joined_ondate
newslettertinyint(1)
notestext

6. Adding Columns with Constraints and Indexes

constraints.sql 
ALTER TABLE customers
  ADD COLUMN pan_number CHAR(10) UNIQUE,
  ADD COLUMN credit_limit DECIMAL(10,2) DEFAULT 10000 CHECK (credit_limit >= 0),
  ADD INDEX idx_city (city);

UPDATE customers SET pan_number = 'ABCDE1234F' WHERE id = 1;
UPDATE customers SET pan_number = 'ABCDE1234F' WHERE id = 2;
Output 
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
ERROR 1062 (23000): Duplicate entry 'ABCDE1234F' for key 'customers.pan_number'

A foreign-key column works the same way; add the column, then the constraint:

fk-column.sql 
CREATE TABLE regions (region_id INT PRIMARY KEY, region_name VARCHAR(20));
INSERT INTO regions VALUES (1, 'West'), (2, 'North'), (3, 'South');
ALTER TABLE customers
  ADD COLUMN region_id INT,
  ADD CONSTRAINT fk_customer_region FOREIGN KEY (region_id) REFERENCES regions(region_id);
UPDATE customers SET region_id = CASE city WHEN 'Pune' THEN 1 WHEN 'Delhi' THEN 2 ELSE 3 END;
SELECT c.name, r.region_name FROM customers c JOIN regions r ON r.region_id = c.region_id;

Result: 3 rows

nameregion_name
IshaanWest
FatimaNorth
GeorgeSouth

7. Adding a Generated Column

A generated column is calculated from other columns. Use VIRTUAL (computed on read) or STORED (saved on write):

generated.sql 
ALTER TABLE customers
  ADD COLUMN email_domain VARCHAR(60)
  AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;

UPDATE customers SET email = CONCAT(LOWER(name), '@mail.example') WHERE id IN (1, 3);
SELECT name, email, email_domain FROM customers;

Result: 3 rows

nameemailemail_domain
Ishaanishaan@mail.examplemail.example
FatimaNULLNULL
Georgegeorge@mail.examplemail.example

8. Performance: INSTANT, INPLACE and COPY

On big tables, how MySQL adds the column matters. Since MySQL 8.0.29, most ADD COLUMN operations (at any position) use the INSTANT algorithm: only metadata changes, so even a table with millions of rows is altered in milliseconds. You can ask for it explicitly; MySQL raises an error rather than silently doing a slow copy if it can't:

query.sql 
ALTER TABLE customers ADD COLUMN referral_code VARCHAR(12), ALGORITHM = INSTANT;
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
AlgorithmHow it worksTable usable during the change?
INSTANTMetadata onlyYes, practically no wait
INPLACERebuilds inside InnoDBMostly yes (reads and writes allowed)
COPYCopies every row into a new tableWrites blocked; slow on big tables
Adding a column that needs a table rebuild (for example a STORED generated column) can take a long time on large tables. Test on a copy, and run it at a quiet time.

9. Adding a Column Only If It Doesn't Exist

MySQL has no ADD COLUMN IF NOT EXISTS. Adding a duplicate fails:

query.sql 
ALTER TABLE customers ADD COLUMN email VARCHAR(100);
Output 
ERROR 1060 (42S21): Duplicate column name 'email'

Check information_schema.columns first, from a script or migration tool:

query.sql 
SELECT COUNT(*) AS email_exists
FROM information_schema.columns
WHERE table_schema = 'crm' AND table_name = 'customers' AND column_name = 'email';

Result: 1 row

email_exists
1
Migration tools such as Flyway and Liquibase track which changes have run, so each ALTER TABLE runs exactly once.

10. Practice Exercises

Exercise 1: Add a gst_number column (15 characters) right after pan_number.
solution.sql 
ALTER TABLE customers ADD COLUMN gst_number CHAR(15) AFTER pan_number;
SHOW COLUMNS FROM customers LIKE '%number';

Result: 2 rows

FieldType
pan_numberchar(10)
gst_numberchar(15)
Exercise 2: Add a required tier column that only accepts 'silver', 'gold' or 'platinum' and defaults to 'silver'.
solution.sql 
ALTER TABLE customers ADD COLUMN tier ENUM('silver','gold','platinum') NOT NULL DEFAULT 'silver';
SELECT name, tier FROM customers;

Result: 3 rows

nametier
Ishaansilver
Fatimasilver
Georgesilver

11. Summary

  • ALTER TABLE t ADD COLUMN c type; adds a column; existing rows get the default or NULL.
  • Use FIRST or AFTER col to choose the position.
  • Give NOT NULL columns an explicit DEFAULT.
  • Add several columns, constraints and indexes in one ALTER TABLE.
  • Most column additions are INSTANT in MySQL 8.0.29+; there is no IF NOT EXISTS, so check information_schema.