MySQL - Add/Delete Columns

MySQL - Modifying Table Structure: Add/Delete Columns

As applications evolve, so do their data requirements. It’s a common task for developers to modify the structure of database tables to accommodate new features or remove obsolete ones. The ALTER TABLE statement in MySQL is the primary tool for these schema migrations, allowing you to add, remove, or modify columns in an existing table.

Adding Columns to a Table

You can add one or more columns to a table using the ALTER TABLE ... ADD COLUMN syntax. You can also specify the position of the new column within the table.

Syntax

ALTER TABLE table_name
    ADD COLUMN new_column_definition [FIRST | AFTER existing_column],
    ADD COLUMN another_column_definition [FIRST | AFTER existing_column];

Key elements:

• column_definition: Includes the column name, data type, and optional constraints like NOT NULL or DEFAULT.
• FIRST: Places the new column at the beginning of the table.
• AFTER existing_column: Places the new column immediately after a specified existing column.
• If no position is specified, the new column is added at the end of the table.

Example: A Step-by-Step Evolution

Let’s start with a basic USERS table.

CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(50) NOT NULL
);

-- Let's view the initial structure
DESCRIBE USERS;

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
USERNAME	varchar(50)	NO		NULL

Now, let’s add an EMAIL column at the end.

ALTER TABLE USERS ADD COLUMN EMAIL VARCHAR(100) NOT NULL;

Next, let’s add a STATUS column with a default value, placing it after USERNAME.

ALTER TABLE USERS ADD COLUMN STATUS VARCHAR(20) NOT NULL DEFAULT 'active' AFTER USERNAME;

Verification

Let’s check the final structure of our table.

DESCRIBE USERS;

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
USERNAME	varchar(50)	NO		NULL
STATUS	varchar(20)	NO		active
EMAIL	varchar(100)	NO		NULL

Deleting Columns from a Table

To remove columns that are no longer needed, you use the ALTER TABLE ... DROP COLUMN statement. This action is irreversible and will permanently delete the column and all of its data, so proceed with caution.

Syntax

ALTER TABLE table_name
    DROP COLUMN column_to_delete,
    DROP COLUMN another_column_to_delete;

Example: Cleaning Up the Table

Let’s say we decide the STATUS column is no longer needed. We can drop it.

ALTER TABLE USERS DROP COLUMN STATUS;

You can verify the change with DESCRIBE USERS;.

Performance and Best Practices for Production

On small tables, ALTER TABLE is fast. On large production tables with millions of rows, it can be a dangerous, long-running operation that locks the table and causes application downtime. Modern MySQL (with the InnoDB storage engine) has improved this with ‘online DDL’ for many operations, but it’s not a silver bullet.

Best Practices

• Combine Operations: When possible, perform multiple additions, modifications, and drops in a single ALTER TABLE statement. This is more efficient than running separate statements for each change.
• Schedule Downtime: For critical tables where online DDL is not possible, plan a maintenance window for schema changes.
• Use Online Schema Change Tools: For zero-downtime migrations on large tables, the industry standard is to use external tools like Percona’s pt-online-schema-change or GitHub’s gh-ost. These tools work by creating a copy of your table, altering the copy, and then carefully migrating data from the original to the new table before swapping them, all without locking the original table for long periods.
• Version Control Migrations: All schema changes should be saved as SQL scripts and committed to a version control system (like Git) alongside your application code. This practice, known as ‘database-as-code’, ensures a traceable and repeatable migration process.

Adding/Deleting a Column Using a Client Program

Database migration scripts are often executed programmatically. Here is a modern PHP example using PDO to manage schema changes, which is a common task in frameworks like Laravel or Symfony.

Example: PHP with PDO

PHP




<?php

// --- Configuration ---
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

// --- Migration Logic ---
try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Database connection successful.\n";

    // --- Migration 1: Add a 'last_login' column ---
    echo "Attempting to add 'last_login' column...\n";
    $sql_add = "ALTER TABLE USERS ADD COLUMN last_login TIMESTAMP NULL DEFAULT NULL AFTER EMAIL";
    $pdo->exec($sql_add);
    echo "Column 'last_login' added successfully.\n";

} catch (PDOException $e) {
    // Check if the error is for a duplicate column
    if ($e->getCode() == '42S21' || strpos($e->getMessage(), 'Duplicate column name') !== false) {
        echo "Column 'last_login' already exists. Skipping.\n";
    } else {
        // Re-throw other errors
        throw $e;
    }
}

try {
    // --- Migration 2: Drop an obsolete 'legacy_id' column (example) ---
    // Let's pretend we added this column before and now want to remove it.
    // We first add it silently to make the example runnable.
    $pdo->exec("ALTER TABLE USERS ADD COLUMN legacy_id INT NULL");

    echo "Attempting to drop 'legacy_id' column...\n";
    $sql_drop = "ALTER TABLE USERS DROP COLUMN legacy_id";
    $pdo->exec($sql_drop);
    echo "Column 'legacy_id' dropped successfully.\n";

} catch (PDOException $e) {
    // Check if the error is for a non-existent column
    if ($e->getCode() == '42703' || strpos($e->getMessage(), 'check that column/key exists') !== false) {
        echo "Column 'legacy_id' does not exist. Skipping.\n";
    } else {
        throw $e;
    }
}

echo "Migration script finished.\n";

?>


Output
The expected output for a successful run:

Database connection successful.
Attempting to add 'last_login' column...
Column 'last_login' added successfully.
Attempting to drop 'legacy_id' column...
Column 'legacy_id' dropped successfully.
Migration script finished.