Rename a column in MySQL

sql

mysql-renaming

sql-syntax

database-management

byAnton Shumikhin·Mar 4, 2025

Rename a column in MySQL with a simple command:

ALTER TABLE `YourTable` CHANGE `OldName` `NewName` Datatype;

Replace YourTable with your table name, OldName with the current column name, NewName with the new column name, and Datatype with the actual data type (e.g., INT, VARCHAR(255)).

Deep dive into the Syntax

This syntax for renaming a column ALTER TABLE table_name CHANGE old_name new_name datatype; replaces the old_name with the new_name. However, it's crucial to specify the datatype again, even if it's not changing. Especially, for earlier versions of MySQL before 8.0, this datatype specification is mandatory.

MySQL version-specific syntax

Syntax for Pre-8.0 MySQL

-- SQL says: "Old is gold, but new is diamond".
ALTER TABLE `customer` CHANGE `customerCity` `customer_city` VARCHAR(225);

For MySQL versions before 8.0, you must define the datatype while renaming a column. Not providing the datatype or mistyping it could lead to an unexpected surprise—or worse, an error.

Syntax for MySQL 8.0 and later

-- New MySQL says: "Brevity is the soul of wit".
ALTER TABLE `table_name` RENAME COLUMN `old_col_name` TO `new_col_name`;

In MySQL 8.0 and onwards, renaming columns got easier and more intuitive because you no longer need to specify the datatype.

Handle with care: Common pitfalls

Typo-Tragedy: Be extra careful while typing the datatype in MySQL versions below 8.0.
Version Dilemma: Always use MySQL version-appropriate commands to rename columns.
Datatype Madness: Recheck your column's datatype before renaming it to avoid unnecessary changes.