MySQL RENAME COLUMN

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.

MySQL 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.

Syntax

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 TO keyword.

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(,).

Example

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.

mysql rename column

Suppose, we want to change the name of the column roll_no to id without changing the datatype. We can do this using the following statement.

ALTER TABLE Students 
RENAME COLUMN roll_no TO id;

Output:

mysql rename column

As you can see from the above image that the name of the roll_no has been changed to the id successfully.

Now, let us take an example of changing multiple columns in a single statement. In the below example, we will change the column name student_age to age and student_email to email.

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 SELECT statement.

mysql rename column

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 email.

MySQL RENAME COLUMN using CHANGE statement

You can also use the CHANGE COLUMN command to rename a column in MySQL.

Syntax

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,
......;

Example

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.

 

mysql rename column

Now we want to change the column name id to roll_no and its datatype to varchar(10) from int. You can use the following statement for the same.

ALTER TABLE Students 
CHANGE COLUMN id roll_no varchar(10);

Output:

mysql rename column

As you can see from the above image that the column name id changed to roll_no and its datatype to varchar(10).

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 firstname, last_name to surname, and the datatype of both the column to varchar(100).

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.

mysql rename column