ALTER TABLE

Add, rename, retype and drop columns on a live table, and use ALGORITHM and LOCK to control what the change costs.

SyntaxALTER TABLE tbl_name alter_option [, alter_option] ... [, ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}] [, LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}]

ALTER TABLE changes an existing table's definition. Several alterations can be listed in one statement, separated by commas, which matters because operations that rebuild the table pay for the rebuild once rather than once each.

The column operations

  • ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  • RENAME COLUMN old_name TO new_name — name only
  • MODIFY [COLUMN] col_name column_definition — definition only
  • CHANGE [COLUMN] old_name new_name column_definition — both at once
  • DROP [COLUMN] col_name

COLUMN is optional noise in every one of these: ADD COLUMN status … and ADD status … are the same statement.

ALGORITHM and LOCK

ALGORITHM takes DEFAULT, INSTANT, INPLACE or COPY; LOCK takes DEFAULT, NONE, SHARED or EXCLUSIVE. Omit ALGORITHM and MySQL 8.4 uses INSTANT where the engine and the clause support it, otherwise INPLACE, otherwise COPY. Adding a column, dropping a column and renaming a column are all instant metadata changes in 8.4; changing a column's data type is not, and only ALGORITHM=COPY supports it.

Naming the algorithm explicitly is a safety device: if the server cannot honour it, the statement fails instead of quietly falling back to a full table copy in the middle of the working day.

What trips people up

Instant ADD/DROP COLUMN creates a new row version each time, and a table is capped at 64 row versions in MySQL 8.4 before it must be rebuilt.

Example

Example · sql
-- One statement, one pass over the table
ALTER TABLE orders
  ADD COLUMN cancelled_at DATETIME NULL AFTER placed_at,
  ADD COLUMN channel VARCHAR(20) NOT NULL DEFAULT 'web',
  RENAME COLUMN notes TO internal_notes,
  DROP COLUMN legacy_ref,
  ALGORITHM = INSTANT;   -- fail loudly rather than rebuild silently

-- Retyping is not instant: only COPY supports it, and COPY blocks DML
ALTER TABLE orders
  MODIFY COLUMN total_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  ALGORITHM = COPY, LOCK = SHARED;   -- reads allowed, writes blocked

-- RENAME COLUMN and CHANGE do the same rename; CHANGE also needs the type
ALTER TABLE orders RENAME COLUMN channel TO source_channel;
ALTER TABLE orders CHANGE COLUMN channel source_channel VARCHAR(20) NOT NULL DEFAULT 'web';

When to use it

  • Add a nullable column to a busy production table without taking writes offline.
  • Widen an INT primary key to BIGINT before it overflows.
  • Rename a column that was misspelled in the original migration.

More examples

Guarding a migration with ALGORITHM and LOCK

Write migrations as a ladder: try INSTANT, fall back to INPLACE with LOCK=NONE, and only reach COPY deliberately. Naming the algorithm converts an unexpected hours-long table copy into an immediate error you can read before anything has happened.

Example · sql
-- Dry run: does this alteration support an instant, lock-free change?
ALTER TABLE events
  ADD COLUMN source VARCHAR(40) NOT NULL DEFAULT 'unknown',
  ALGORITHM = INSTANT;
-- If the server cannot do it instantly:
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

-- Second choice: rebuild, but keep the table writable throughout
ALTER TABLE events
  ADD INDEX idx_events_source (source),
  ALGORITHM = INPLACE, LOCK = NONE;

-- Only when you have accepted the downtime
ALTER TABLE events
  MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ALGORITHM = COPY, LOCK = SHARED;

How MODIFY silently drops attributes

MODIFY and CHANGE take a complete column definition and replace the old one wholesale — there is no merge. The habit worth forming is to copy the line out of SHOW CREATE TABLE, edit the one part you mean to change, and paste the whole line back.

Example · sql
-- Current definition, from SHOW CREATE TABLE:
--   `email` varchar(255) NOT NULL DEFAULT '' COMMENT 'login identity'

-- The careless widening: NOT NULL, DEFAULT and COMMENT are all gone
ALTER TABLE users MODIFY COLUMN email VARCHAR(320);

-- The correct widening: restate everything you want to keep
ALTER TABLE users
  MODIFY COLUMN email VARCHAR(320) NOT NULL DEFAULT '' COMMENT 'login identity';

Instant column changes and the 64-version ceiling

Relevant on tables that accumulate schema churn from frequent feature migrations. ALTER TABLE ... ENGINE=InnoDB (and its shorthand, FORCE) rebuilds the table in place, which compacts it, refreshes statistics, and clears the accumulated row versions so instant DDL works again.

Example · sql
-- Each instant ADD or DROP COLUMN records a new row version.
-- MySQL 8.4 permits 64 per table.
ALTER TABLE wide_table ADD COLUMN flag_65 TINYINT(1) NOT NULL DEFAULT 0;
-- ERROR 4092 (HY000): Maximum row versions reached for table test/wide_table.
-- No more columns can be added or dropped instantly.

-- Reset the version count by rebuilding the table once
ALTER TABLE wide_table ENGINE = InnoDB, ALGORITHM = INPLACE, LOCK = NONE;
-- Or equivalently:
ALTER TABLE wide_table FORCE;

Related

sql create tablesql data typessql create indexsql drop truncatesql generated columns

Discussion

  • Be the first to comment on this lesson.