Summary: in this tutorial, you will learn how to rename a column of a table using MySQL
RENAME COLUMN and
CHANGE COLUMN statements with examples.
Sometimes we may need to change a column name of a MySQL table. MySQL allows us to modify its objects using just a few commands.
RENAME COLUMN is used to change the column name of a MySQL table. This command is used along with the ALTER TABLE statement.
You can rename a column name in MySQL in two ways:
- Using RENAME statement
- Using CHANGE statement
MySQL RENAME COLUMN using RENAME statement
This is the commonly used command to change a column name.
Below is the basic syntax of renaming a column in MySQL.
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
The syntax is very simple here,
First, specify the name of the table whose column you are going to rename after the
ALTER TABLE keywords.
Second, specify the name of the old column name after the
RENAME COLUMN keywords.
And Finally, provide the new column name after the
You can also change the name of multiple columns in a single statement. Below is the syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name1 TO new_column_name1, RENAME COLUMN old_column_name2 TO new_column_name2, ... ...;
Here, you need to specify each
RENAME COLUMN statement separated by comma(
Let’s understand the concept of renaming columns with the help of a few examples. Suppose, we have a
Students table with the following data.
Suppose, we want to change the name of the column
id without changing the datatype. We can do this using the following statement.
ALTER TABLE Students RENAME COLUMN roll_no TO id;
As you can see from the above image that the name of the
roll_no has been changed to the
Now, let us take an example of changing multiple columns in a single statement. In the below example, we will change the column name
ALTER TABLE Students RENAME COLUMN student_age TO age, RENAME COLUMN student_email to email;
After successful completion of the above statement, let’s check the table using the
From the above output, you can notice that the name of the
student_age column changed to
age and the name of the
student_email column changed to only
MySQL RENAME COLUMN using CHANGE statement
You can also use the
CHANGE COLUMN command to rename a column in MySQL.
You can use the following syntax to change the name of the column using the
CHANGE COLUMN statement.
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name Data Type;
Notice that you can also change the data type of the column along with the name. However, this comes with a few disadvantages. They are:
- All the column attributes might not be available to the application for renaming.
- Wrong datatype change might result in the application’s data loss.
You can also rename multiple columns in a single statement. The syntax for the same is as below:
ALTER TABLE table_name CHANGE COLUMN old_column_name1 new_column_name1 Data Type, CHANGE COLUMN old_column_name2 new_column_name2 Data Type, ......;
Let us understand the concept of changing column names using the
CHANGE statement with the help of some examples. Suppose, we have a
Students table as below.
Now we want to change the column name
roll_no and its datatype to
int. You can use the following statement for the same.
ALTER TABLE Students CHANGE COLUMN id roll_no varchar(10);
As you can see from the above image that the column name
id changed to
roll_no and its datatype to
Now we will check how to rename multiple columns in a single statement using the
CHANGE keyword. In this example, we will change the name of the
first_name column to
surname, and the datatype of both the column to
ALTER TABLE Students CHANGE COLUMN firstname first_name varchar(50), CHANGE COLUMN surname last_name varchar(50);
Finally, if we do
DESCRIBE TABLE on
Students then we can see the changes.