How to Add a Column to a MySQL Table

The shape of the command for adding a column to a MySQL table is ALTER TABLE ... ADD COLUMN ..., and it has been the same on every version since 4.x. The interesting parts are what you can add to it: a DEFAULT value so existing rows are not left with NULL, an AFTER clause to control where the column sits in the row, and (on MySQL 8.0.12 and later) an ALGORITHM=INSTANT clause that makes the change finish in milliseconds even on huge tables. I'll walk all of these on a subscribers table that needs a new consent_version column for GDPR / CCPA tracking, which is the most common real-world reason someone adds a column to an existing production table.

CREATE TABLE subscribers (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email         VARCHAR(255) NOT NULL,
  first_name    VARCHAR(80)  NOT NULL,
  subscribed_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_email (email)
);

Jump to:

• The simplest form: ADD COLUMN
• With a DEFAULT so existing rows are not NULL
• Adding a NOT NULL column on a table that already has rows
• Controlling position with AFTER and FIRST
• ALGORITHM=INSTANT for big tables (MySQL 8.0.12+)
• FAQ

The simplest form: ADD COLUMN

The shortest correct form. Works in MySQL 5.5, 5.6, 5.7, 8.0, and 8.4.

ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16);

Two things happen when this runs:

1. A new column called :column is appended to the table at the end of the row layout.
2. Every existing row gets NULL in the new column, because no DEFAULT is specified and the column is nullable.

If the table is small (under a few million rows) this finishes in seconds and no further consideration is needed. On large tables it can take minutes and hold locks for the duration; see the section on ALGORITHM=INSTANT below for the modern alternative.

With a DEFAULT so existing rows are not NULL

If a NULL value for the new column would be a bug — and it usually is, for GDPR-style consent tracking it definitely is — give the column a DEFAULT so every existing row gets a sensible value.

ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16) NOT NULL DEFAULT 'v1.0';

Now every existing row has :column = 'v1.0' and new rows that do not specify the column will also get 'v1.0'. This is the form to use for backfilling a flag, a version marker, or a status enum across a table that already has data.

The performance characteristic: on MySQL 8.0.12 and later, adding a column with a DEFAULT is an instant metadata-only operation thanks to ALGORITHM=INSTANT (covered below). On 5.7, the entire table is rewritten — fast on small tables, slow on big ones.

Adding a NOT NULL column on a table that already has rows

If the column has to be NOT NULL and you do not want to pick a default value for it, the safe pattern is three statements:

-- 1. Add as nullable
ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16) NULL;

-- 2. Backfill the values you actually want
UPDATE subscribers
SET consent_version = CASE
WHEN subscribed_at < '2020-01-01' THEN 'legacy'
WHEN subscribed_at < '2024-01-01' THEN 'v1.0'
ELSE 'v2.0'
END;

-- 3. Tighten the column to NOT NULL
ALTER TABLE subscribers
MODIFY COLUMN consent_version VARCHAR(16) NOT NULL;

The three steps separate the structural change (add the column) from the data change (compute the values for each row) from the constraint change (lock it down). This is the pattern that lets you do meaningful backfills, like "everyone who signed up before 2020 gets 'legacy', everyone after gets 'v2.0'", instead of one blanket default.

In a production deployment, you typically run step 1 and 2 first, deploy the application code that reads and writes the new column, and then run step 3 last after a verification window. This avoids the race where new inserts arrive before the backfill finishes.

Controlling position with AFTER and FIRST

By default the new column goes at the end of the row. To put it in a specific position, use AFTER another_column or FIRST.

-- Place the new column right after first_name
ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16) NOT NULL DEFAULT 'v1.0'
AFTER first_name;

-- Or put it at the very beginning
ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16) NOT NULL DEFAULT 'v1.0'
FIRST;

Position purely affects how the row is stored on disk and how SELECT * orders the columns. It has no functional impact, but it is sometimes worth doing for readability — keeping all PII-adjacent columns together, for example, so a DESCRIBE table reads in a logical order.

Caveat for production tables on MySQL 8.0.12+: specifying AFTER or FIRST disables ALGORITHM=INSTANT, because reordering columns requires a table rebuild. If the table is huge, prefer adding at the end and living with the imperfect DESCRIBE order.

ALGORITHM=INSTANT for big tables (MySQL 8.0.12+)

The headline feature for schema migrations on MySQL 8.0.12 and later. An "instant" column addition is metadata-only — the existing rows are not rewritten, and the change completes in milliseconds regardless of table size.

ALTER TABLE subscribers
ADD COLUMN consent_version VARCHAR(16) NOT NULL DEFAULT 'v1.0',
ALGORITHM=INSTANT, LOCK=NONE;

ALGORITHM=INSTANT tells MySQL to use the instant algorithm; the statement fails immediately if MySQL cannot satisfy it (rather than silently falling back to the slow path). This is the behaviour you want — better a clear failure than a 4-hour table rewrite during business hours.

The rules for when INSTANT is allowed:

• Adding a column at the end of the table (no AFTER or FIRST)
• With a literal DEFAULT (no expressions)
• Not a generated column
• Not a virtual column
• The column type is one of the supported ones (most basic types are)

If your ALTER does not qualify, the statement errors with ALGORITHM=INSTANT is not supported for this operation, and you fall back to ALGORITHM=INPLACE (table rebuilt but other DDL still works) or ALGORITHM=COPY (full table rebuild, locks for the duration). For tables over a few hundred million rows, you usually want a tool like gh-ost or pt-online-schema-change for INPLACE/COPY cases.

Comparison: which ADD COLUMN approach to pick

What to do next

If you need to change an existing column's type rather than add a new one — for example, widening VARCHAR(50) to VARCHAR(100) or upgrading INT to BIGINT — that's a different operation with its own data-preservation considerations: How to Change a MySQL Column Type walks through MODIFY vs CHANGE, what types convert cleanly, and how ALGORITHM=INSTANT applies (or does not) to type changes.

For the wider reference on ALTER TABLE options, indexes, and constraints, the MySQL Cheat Sheet keeps the syntax in one searchable place.

External reference: official documentation on ALTER TABLE syntax and the InnoDB instant ADD COLUMN feature.

FAQ