Altering Tables — MySQL

ALTER TABLE in MySQL

The ALTER TABLE statement modifies an existing table's structure without dropping and recreating it. You can add or remove columns, change data types, manage indexes and foreign keys, and rename objects — all while keeping your data intact.

Adding a Column

Use ADD COLUMN to append a new column. By default the column is added at the end of the table.

SQL

ALTER TABLE employees
  ADD COLUMN middle_name VARCHAR(100) NULL AFTER first_name;

Note

The AFTER col_name clause places the new column immediately after an existing one. Use FIRST to place it at the beginning.

SQL

-- Add multiple columns at once
ALTER TABLE employees
  ADD COLUMN phone VARCHAR(20) NULL,
  ADD COLUMN hire_date DATE NOT NULL DEFAULT '2024-01-01';

Dropping a Column

SQL

ALTER TABLE employees
  DROP COLUMN middle_name;

Warning

Dropping a column is irreversible and permanently deletes all data in that column. Always back up your data first.

Modifying a Column — MODIFY vs CHANGE

MySQL gives you two keywords for changing columns:

MODIFY COLUMN — change the definition but keep the same name
CHANGE COLUMN — rename the column and/or change its definition

SQL

-- Change the data type of an existing column
ALTER TABLE employees
  MODIFY COLUMN phone VARCHAR(30) NOT NULL;

-- Rename a column and change its type
ALTER TABLE employees
  CHANGE COLUMN phone mobile_phone VARCHAR(30) NOT NULL;

Tip

Always specify the full column definition when using MODIFY or CHANGE, including NOT NULL, DEFAULT, etc. MySQL replaces the entire definition, not just the part you changed.

Renaming a Column (MySQL 8.0+)

MySQL 8.0 introduced RENAME COLUMN which renames a column without redefining its type — much safer.

SQL

-- MySQL 8.0+ only
ALTER TABLE employees
  RENAME COLUMN mobile_phone TO phone_number;

Renaming a Table

SQL

ALTER TABLE employees RENAME TO staff;

-- Alternatively use the dedicated statement
RENAME TABLE employees TO staff;

-- Rename multiple tables atomically
RENAME TABLE employees TO staff, departments TO teams;

Changing Column Order

SQL

-- Move 'hire_date' to appear right after 'last_name'
ALTER TABLE employees
  MODIFY COLUMN hire_date DATE NOT NULL AFTER last_name;

-- Move a column to the very first position
ALTER TABLE employees
  MODIFY COLUMN employee_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

Note

Column order rarely matters functionally in well-written code but can affect readability in SELECT * output. Avoid reordering large production tables frequently — it triggers a full table rebuild.

Adding an Index

SQL

-- Add a regular index
ALTER TABLE employees
  ADD INDEX idx_last_name (last_name);

-- Add a unique index
ALTER TABLE employees
  ADD UNIQUE INDEX uq_email (email);

-- Add a composite index
ALTER TABLE orders
  ADD INDEX idx_customer_date (customer_id, order_date);

-- Add a full-text index
ALTER TABLE articles
  ADD FULLTEXT INDEX ft_body (body);

Dropping an Index

SQL

ALTER TABLE employees
  DROP INDEX idx_last_name;

-- Drop the primary key
ALTER TABLE employees
  DROP PRIMARY KEY;

-- Add and drop in one statement (atomic, single pass)
ALTER TABLE employees
  DROP INDEX idx_last_name,
  ADD INDEX idx_full_name (last_name, first_name);

Adding a Foreign Key

SQL

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT;

Tip

Name your foreign keys explicitly (e.g. fk_orders_customer). Auto-generated names are hard to reference later when you need to drop the constraint.

Dropping a Foreign Key

SQL

-- First find the constraint name if you forgot it
SELECT CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'orders'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- Then drop it
ALTER TABLE orders
  DROP FOREIGN KEY fk_orders_customer;

Changing the Table Engine

SQL

-- Convert a MyISAM table to InnoDB
ALTER TABLE legacy_table ENGINE = InnoDB;

Warning

Converting from MyISAM to InnoDB rewrites the entire table and can take a long time for large datasets. Schedule this during a maintenance window.

Changing Charset and Collation

SQL

-- Change table default charset (affects new columns only)
ALTER TABLE articles
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Also convert existing column data
ALTER TABLE articles
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

ALTER TABLE Gotchas and Locking

Table Locks on Large Tables

In older MySQL versions (before 5.6), most ALTER TABLE operations acquired an exclusive lock, blocking all reads and writes for the duration. On a table with millions of rows this could mean hours of downtime.

MySQL 5.6+ introduced Online DDL (in InnoDB) which allows many operations to run concurrently with DML. MySQL 8.0 expanded online DDL support further.

Online DDL: ALGORITHM and LOCK Clauses

SQL

-- ALGORITHM=INSTANT: zero-copy, no rebuild (MySQL 8.0+)
-- ALGORITHM=INPLACE: rebuild in place, allows concurrent DML
-- ALGORITHM=COPY:    full table copy, slowest

-- MySQL will error if the requested algorithm is not supported
ALTER TABLE orders
  ADD COLUMN notes TEXT NULL,
  ALGORITHM=INSTANT;

-- For index additions, explicitly allow concurrent reads
ALTER TABLE orders
  ADD INDEX idx_status (status),
  ALGORITHM=INPLACE,
  LOCK=NONE;

Operation	INSTANT	INPLACE	COPY
ADD COLUMN (not first/after)	Yes (8.0+)	Yes	Yes
DROP COLUMN	No	Yes	Yes
ADD INDEX	No	Yes	Yes
DROP INDEX	No	Yes	Yes
CHANGE/MODIFY COLUMN type	No	Sometimes	Yes
ADD FOREIGN KEY	No	Yes	Yes
RENAME COLUMN (8.0+)	Yes	Yes	Yes

pt-online-schema-change

For very large tables or environments running older MySQL, Percona Toolkit's pt-online-schema-change (pt-osc) is the industry-standard solution. It:

Creates a shadow table with the new schema.
Copies rows in small batches.
Uses triggers to replay ongoing DML on the original table to the shadow.
Swaps the tables atomically when done.

Bash

# Install Percona Toolkit, then:
pt-online-schema-change \
  --alter "ADD COLUMN notes TEXT NULL" \
  --user=root --password=secret \
  --host=127.0.0.1 \
  D=mydb,t=orders \
  --execute

Note

gh-ost (GitHub online schema change tool) is a popular alternative to pt-osc. It uses the binary log instead of triggers, making it safer for replication-heavy setups.

Practical Example: Evolving an Orders Table

SQL

-- Original table
CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Step 1: Add a status column with a default
ALTER TABLE orders
  ADD COLUMN status ENUM('pending','processing','shipped','delivered','cancelled')
    NOT NULL DEFAULT 'pending'
  AFTER total,
  ALGORITHM=INSTANT;

-- Step 2: Add an index for filtering by status
ALTER TABLE orders
  ADD INDEX idx_customer_status (customer_id, status),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Step 3: Add a foreign key (assumes customers table exists)
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- Step 4: Rename a poorly named column (MySQL 8.0+)
ALTER TABLE orders
  RENAME COLUMN total TO amount_total;

Useful Metadata Queries

SQL

-- View current table structure
DESCRIBE orders;
SHOW CREATE TABLE orders\G

-- View all indexes on a table
SHOW INDEX FROM orders;

-- View foreign keys from information_schema
SELECT
  CONSTRAINT_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Best Practices

Test every ALTER TABLE on a staging environment first — especially on large tables.
Prefer ALGORITHM=INSTANT for MySQL 8.0 when adding nullable columns or enum values.
Batch multiple column changes into a single ALTER TABLE statement to minimise rebuild passes.
Use pt-online-schema-change or gh-ost for tables larger than a few hundred GB in production.
Always name constraints explicitly so you can reference them easily later.
Monitor replication lag after DDL on replicas — INPLACE operations can still cause brief lag spikes.
Keep a DDL change log so you can reproduce the schema evolution in DR environments.