MySQL - Alter Tables

MySQL - ALTER TABLE Command

What is the ALTER TABLE Command?

The ALTER TABLE command is a powerful Data Definition Language (DDL) statement used to modify the structure of an existing table. It allows you to perform a wide variety of schema changes, including adding, dropping, or modifying columns, as well as changing table properties and managing constraints.

It’s crucial to distinguish ALTER from UPDATE. ALTER changes the table’s structure (the blueprint), while UPDATE changes the data inside the table (the records).

Basic Syntax

ALTER TABLE table_name
    [alter_specification [, alter_specification] ...];

alter_specification can be an action like ADD COLUMN, DROP COLUMN, RENAME TO, etc. Let’s start with a sample table.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

Adding a New Column

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

ALTER TABLE products ADD COLUMN price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

You can control the position of the new column using FIRST or AFTER.

-- Add a 'sku' column after the 'id' column
ALTER TABLE products ADD COLUMN sku VARCHAR(50) UNIQUE AFTER id;

Dropping an Existing Column

To remove a column, use the DROP COLUMN clause. Be careful, as this action permanently deletes the column and all its data.

-- Let's add a temporary column and then drop it
ALTER TABLE products ADD COLUMN temp_notes TEXT;
ALTER TABLE products DROP COLUMN temp_notes;

Modifying a Column’s Definition (MODIFY vs. CHANGE)

MySQL provides two ways to alter a column’s definition: MODIFY and CHANGE. They have a subtle but important difference.

Using MODIFY

MODIFY is used to change a column’s data type, default value, or other attributes, but it cannot rename the column.

-- Change the 'sku' column to be non-unique and longer
ALTER TABLE products MODIFY COLUMN sku VARCHAR(100) NULL;

Using CHANGE

CHANGE can do everything MODIFY can do, and it can also rename the column. Its syntax requires you to specify the old name and the new name, even if they are the same.

-- Rename 'name' to 'product_name' and make it longer
ALTER TABLE products CHANGE COLUMN name product_name VARCHAR(500) NOT NULL;

If you only want to change the type with CHANGE, you must repeat the column name:

ALTER TABLE products CHANGE COLUMN price price DECIMAL(12, 2) NOT NULL;

Renaming a Table

To rename the entire table, use the RENAME TO clause.

ALTER TABLE products RENAME TO inventory;

You can verify the change by trying to describe the new table name:

DESCRIBE inventory;

Best Practices for Production Environments

Running ALTER TABLE on a large table in a live production environment can be risky. It can lock the table for a long time, causing downtime for your application. Modern practices are essential:

Online DDL: Newer MySQL versions (5.6+) and InnoDB support online DDL for many operations using the ALGORITHM=INPLACE and LOCK=NONE clauses. This allows some schema changes to occur without locking the table against reads and writes. Always check the documentation for your specific operation and MySQL version.
Online Schema Change Tools: For major changes or on older MySQL versions, dedicated tools like Percona’s pt-online-schema-change or GitHub’s gh-ost are the gold standard. They work by creating a new, altered table, copying data from the old table in small chunks, and using triggers to capture changes made during the process. Once complete, they atomically swap the tables with zero downtime.
Schema Migration Tools: Use a schema version control tool like Flyway or Liquibase in your development workflow. These tools track database changes in versioned script files, making it easy to apply, roll back, and manage schema evolution across different environments (development, staging, production) in a repeatable and reliable way.
Backups: Before any major schema change, always ensure you have a recent, restorable backup of your database.
Testing: Test your schema changes in a staging environment that mirrors your production setup to identify any performance issues or unexpected behavior.

Altering a Table via a Client Program

Executing an ALTER TABLE statement from a client application is no different from running any other DDL query. However, due to the potentially long execution time, you may need to adjust connection timeouts.

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// Assuming $pdo is a connected PDO object

try {
    echo "Adding 'is_active' column to inventory table...\n";
    $sql = "ALTER TABLE inventory ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE";
    $pdo->exec($sql);
    echo "Table altered successfully.\n";
} catch (PDOException $e) {
    echo "Error altering table: " . $e->getMessage() . "\n";
}
?>

const mysql = require('mysql2/promise');

async function addColumn() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });
        console.log("Adding 'last_updated' column to inventory table...");
        const sql = "ALTER TABLE inventory ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
        await connection.query(sql);
        console.log('Table altered successfully.');
    } catch (error) {
        console.error('Error altering table:', error);
    } finally {
        if (connection) await connection.end();
    }
}

addColumn();

import java.sql.*;

public class SchemaChanger {
    // Assuming DB_URL, USER, PASS are defined
    public static void main(String[] args) {
        String sql = "ALTER TABLE inventory ADD COLUMN in_stock_quantity INT UNSIGNED NOT NULL DEFAULT 0";
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            System.out.println("Adding 'in_stock_quantity' column to inventory table...");
            stmt.executeUpdate(sql);
            System.out.println("Table altered successfully.");
        } catch (SQLException e) {
            System.err.println("Error altering table: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

import mysql.connector

def add_column_to_inventory():
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor()

        print("Adding 'notes' column to inventory table...")
        sql = "ALTER TABLE inventory ADD COLUMN notes TEXT NULL"
        cursor.execute(sql)
        conn.commit()
        print("Table altered successfully.")

    except mysql.connector.Error as err:
        print(f"Error altering table: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    add_column_to_inventory()