How to Rename a Column in MySQL?

When working with databases, there are times when we need to change the structure of our tables. A common task in this regard is renaming columns, a process that helps maintain data integrity and accommodates evolving needs.

In this article, We will learn how to rename a column in MySQL using different methods.

How to Rename a Column in MySQL

To rename a column in MySQL use the ALTER TABLE Statement with the CHANGE or RENAME clause. Both Change and Rename can be used to change the name of the SQL table column, The only difference is that CHANGE can be utilized to alter the datatype of the column.

Syntax

Syntax for Change Clause

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype;

Syntax for Rename Clause

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Parameters

• table_name: The name of the table containing the column.
• old_column_name: The current name of the column.
• new_column_name: The shiny new name we want for our column.
• datatype: The data type the column should hold.

Demo MySQL Database

For this tutorial on how to rename a table column in MySQL, we will use the following database.

employee_id	name	department	phone_number
1	John Doe	Sales	NULL
2	Jane Smith	Marketing	555-1234
3	Bob Johnson	Sales	NULL

To create this table, copy-paste this MySQL Query in your MySQL Workbench:

MySQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    phone_number VARCHAR(15)
);

INSERT INTO employees VALUES
(1, 'John Doe', 'Sales', NULL),
(2, 'Jane Smith', 'Marketing', '555-1234'),
(3, 'Bob Johnson', 'Sales', NULL);

Rename a Column in MySQL Examples

For this example, we have a table called employees, and we want to change the name of the emp_name column, to full_name.

Example 1: Rename Column Using ALTER TABLE Statement with CHANGE Clause

ALTER TABLE employees
CHANGE emp_name full_name VARCHAR(255);

Output:

After altering the column name as full_name

Example 2: Rename Column Using ALTER TABLE Statement with Rename Clause

ALTER TABLE employees RENAME emp_name TO full_name;

How to Rename Multiple Columns in MySQL

To rename multiple columns in MySQL, you can adjust the syntax to:

For CHANGE Clause:

ALTER TABLE table_name
RENAME COLUMN old_column_name1 TO new_col_name1,
RENAME COLUMN old_column_name2 TO new_col_name2,
RENAME COLUMN old_column_name3 TO new_col_name3;

For RENAME Clause:

ALTER TABLE table_name
CHANGE old_column_name1 new_col_name1 Data Type,
CHANGE old_column_name2 new_col_name2 Data Type,
CHANGE old_column_name3 new_col_name3 Data Type;

Which One to Use

Both CHANGE and RENAME are SQL commands used to modify the name of a column in a table, but they serve different purposes and are used in different scenarios:

• If you need to change both the name and data type of a column, or modify other properties along with the name change, then CHANGE is more appropriate.
• If you only need to rename the column without altering its data type or other attributes, then RENAME is the preferred choice.

In summary, choose ‘CHANGE ‘ when you need to modify multiple aspects of the column, and use ‘RENAME ‘ when you only need to change the column name.