MySQL DDL

How to Add a Column in MySQL (ALTER TABLE Examples)

Add columns to MySQL tables with ALTER TABLE. Covers ADD COLUMN, default values, position control, multiple columns, and online DDL.

Mar 12, 2026 4 min read

Introduction

ALTER TABLE ADD COLUMN is one of the most common DDL operations. MySQL makes it straightforward, with options to control column position and default values.

Add a Single Column

The basic syntax adds a column at the end of the table.

-- Add a column with default value:
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) DEFAULT NULL;

-- Add a NOT NULL column with default:
ALTER TABLE users
ADD COLUMN status ENUM('active', 'inactive') NOT NULL DEFAULT 'active';

Tip: Adding a column with a DEFAULT is instant in MySQL 8.0+ (instant DDL). The default is stored in metadata, not written to every row.

Control Column Position

Use AFTER or FIRST to place the column at a specific position.

-- Add after a specific column:
ALTER TABLE users
ADD COLUMN middle_name VARCHAR(100) AFTER first_name;

-- Add as the first column:
ALTER TABLE users
ADD COLUMN uuid CHAR(36) FIRST;

Tip: Column position is cosmetic — it doesn't affect performance. But it helps readability in SELECT * results.

Add Multiple Columns at Once

Add several columns in a single ALTER TABLE statement to minimize table rebuilds.

-- Add multiple columns:
ALTER TABLE products
ADD COLUMN weight DECIMAL(10,2) DEFAULT NULL,
ADD COLUMN dimensions VARCHAR(50) DEFAULT NULL,
ADD COLUMN is_fragile BOOLEAN DEFAULT FALSE;

Tip: Always add multiple columns in a single ALTER TABLE when possible. Each ALTER TABLE may rebuild the table in older MySQL versions.

Add Column with Index

Create a column and its index together for efficiency.

-- Add column and index in one statement:
ALTER TABLE orders
ADD COLUMN tracking_number VARCHAR(100) DEFAULT NULL,
ADD INDEX idx_orders_tracking (tracking_number);

Tip: Combining ADD COLUMN and ADD INDEX in one ALTER TABLE is faster than running them separately.

Online DDL (No Downtime)

MySQL 8.0 supports instant and in-place DDL for most column additions.

-- Instant DDL (fastest, no table rebuild):
ALTER TABLE users
ADD COLUMN preferences JSON DEFAULT NULL,
ALGORITHM=INSTANT;

-- In-place DDL (allows concurrent reads/writes):
ALTER TABLE users
ADD COLUMN bio TEXT DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

-- Check if instant DDL is supported:
-- ALGORITHM=INSTANT works for:
-- Adding columns (MySQL 8.0.12+)
-- Adding/dropping virtual generated columns
-- Changing column default values

Tip: ALGORITHM=INSTANT is the best option when available. It modifies only metadata — no data is touched.

Best Practices

Always specify DEFAULT for new columns to avoid NULL in existing rows
Use ALGORITHM=INSTANT in MySQL 8.0+ for zero-downtime column additions
Combine multiple column additions in a single ALTER TABLE
Test ALTER TABLE on a copy of the table first for large tables
Add NOT NULL columns with a DEFAULT to avoid breaking existing queries

Generate MySQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct MySQL query instantly.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

Does adding a column lock the table?

In MySQL 8.0+, most ADD COLUMN operations use instant DDL which is virtually instantaneous. In older versions, it may rebuild the table and lock it briefly.

Can I add a column at a specific position?

Yes. Use AFTER column_name to place it after a specific column, or FIRST to add it as the first column.

Can AI2SQL generate ALTER TABLE statements?

Yes. Tell AI2SQL "add a phone number column to the users table" and it generates the correct ALTER TABLE with appropriate data type.