MySQL - Rename Columns

MySQL - Renaming Table Columns

As your application evolves, you’ll often need to modify your database schema. Renaming columns is a common task, whether to improve clarity, conform to new naming conventions, or fix a typo. MySQL provides two primary ways to do this using the ALTER TABLE statement.

Note: Renaming a column requires the ALTER privilege on the table.

Method 1: RENAME COLUMN (Simple Renaming)

Use the RENAME COLUMN clause when your only goal is to change a column’s name without altering its data type or any other attributes. This is the simplest and safest option for a direct rename.

Syntax

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Example

First, let’s create a sample Products table:

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    qty INT NOT NULL
);

Let’s check its structure:

DESCRIBE Products;

The output shows the column qty.

Field	Type	Null	Key	Default	Extra
product_id	int	NO	PRI	NULL	auto_increment
product_name	varchar(255)	NO		NULL
qty	int	NO		NULL

Now, let’s rename the qty column to quantity_on_hand for better clarity.

ALTER TABLE Products
RENAME COLUMN qty TO quantity_on_hand;

Verification

Running DESCRIBE Products; again shows the updated column name, while the data type and other attributes remain unchanged.

Field	Type	Null	Key	Default	Extra
product_id	int	NO	PRI	NULL	auto_increment
product_name	varchar(255)	NO		NULL
quantity_on_hand	int	NO		NULL

Method 2: CHANGE COLUMN (Renaming and Redefining)

Use the CHANGE COLUMN clause when you need to rename a column AND/OR modify its definition, such as changing its data type, NULL constraint, or default value.

Syntax

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_column_definition;

Common Pitfall

You must provide the entire new column definition, including data type and constraints. If you omit it, MySQL may apply a default definition, which could unexpectedly alter your column.

Example

Let’s add a price column to our Products table and then modify it.

ALTER TABLE Products ADD COLUMN price DECIMAL(8, 2);

-- Current structure:
DESCRIBE Products;

Now, we want to rename price to unit_price and also increase its precision to allow for larger values.

ALTER TABLE Products
CHANGE COLUMN price unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

Verification

Describing the table now shows the new name, new data type, NOT NULL constraint, and default value.

Field	Type	Null	Key	Default	Extra
product_id	int	NO	PRI	NULL	auto_increment
product_name	varchar(255)	NO		NULL
quantity_on_hand	int	NO		NULL
unit_price	decimal(10,2)	NO		0.00

Practical Considerations for Live Systems

• Table Locking: ALTER TABLE operations can lock the table, blocking reads and writes. On large tables, this can cause significant downtime. Modern versions of MySQL/InnoDB have improved online DDL capabilities to minimize locking, but it’s still a major consideration.
• Application Impact: Renaming a column is a breaking change for your application. You must coordinate the database change with a code deployment that updates all references to the column.
• Data Conversion: Changing a data type (e.g., from VARCHAR(100) to VARCHAR(50)) can cause data truncation. Always test schema changes in a staging environment first.
• Use Migration Scripts: In a professional setting, manage all schema changes through version-controlled migration scripts using tools like Flyway or Liquibase. This ensures changes are documented, tested, and repeatable across all environments (dev, staging, production).

Renaming Columns via Client Applications

Executing an ALTER TABLE statement from code is straightforward. The examples below show how to do this safely in various languages.

PHP
Node.js
Java
Python



// PHP (using PDO for a more database-agnostic approach)

$host = getenv('DB_HOST') ?: 'localhost';
$db   = getenv('DB_NAME') ?: 'TUTORIALS';
$user = getenv('DB_USER') ?: 'root';
$pass = getenv('DB_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,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Connected successfully.\n";

     $sql = "ALTER TABLE Products RENAME COLUMN qty TO quantity_on_hand;";
     $pdo->exec($sql);

     echo "Column renamed successfully!";

} catch (\PDOException $e) {
     // In production, log this error instead of echoing it
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}



// Node.js (using mysql2/promise)

import mysql from 'mysql2/promise';

async function renameTableColumn() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASS || 'password',
            database: process.env.DB_NAME || 'TUTORIALS'
        });

        // Note: DDL statements don't support parameter binding for table/column names.
        // Ensure these names are sanitized and not from user input to prevent injection.
        const tableName = 'Products';
        const oldColName = 'qty';
        const newColName = 'quantity_on_hand';

        const sql = `ALTER TABLE ${tableName} RENAME COLUMN ${oldColName} TO ${newColName}`;
        await connection.execute(sql);

        console.log(`Column in table '${tableName}' renamed successfully.`);

    } catch (error) {
        console.error('Failed to rename column:', error);
    } finally {
        if (connection) await connection.end();
    }
}

renameTableColumn();



// Java (using JDBC with try-with-resources)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class RenameColumnExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";

        // DDL commands cannot use prepared statements for identifiers.
        // Ensure table/column names are from a safe source.
        String sql = "ALTER TABLE Products RENAME COLUMN qty TO quantity_on_hand";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement stmt = conn.createStatement()) {

            System.out.println("Executing schema change...");
            stmt.executeUpdate(sql);
            System.out.println("Column renamed successfully!");

        } catch (SQLException e) {
            System.err.println("Database schema change failed.");
            e.printStackTrace();
        }
    }
}



# Python (using mysql-connector-python)

import mysql.connector
import os

def rename_column():
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASS', 'password'),
            database=os.getenv('DB_NAME', 'TUTORIALS')
        )
        cursor = connection.cursor()

        # Warning: Do not format user input into this query string.
        rename_query = "ALTER TABLE Products RENAME COLUMN qty TO quantity_on_hand"

        cursor.execute(rename_query)
        print("Column renamed successfully.")

    except mysql.connector.Error as err:
        print(f"Failed to rename column: {err}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

if __name__ == "__main__":
    rename_column()