MySQL Rename Column

Sometimes our column name is non-meaningful, so it is required to rename or change the column’s name. MySQL provides a useful syntax that can rename one or more columns in the table. Few privileges are essential before renaming the column, such as ALTER and DROP statement privileges.

MySQL can rename the column name in two ways:

1. Using the CHANGE statement
2. Using the RENAME statement

Using the CHANGE Statement:

The following are the syntax that illustrates the column rename using the CHANGE statement:

ALTER TABLE table_name   

CHANGE COLUMN old_column_name new_column_name Data Type;

 

In this syntax, we can see that we may require re-specification of all the column attributes. This syntax can also allow us to change the column’s data types. But, sometimes the CHANGE statement might have the following disadvantages:

• All information of column attributes might not be available to the application for renaming.
• There is a risk of accidental data type change that might result in the application’s data loss.

Example

Let us understand how the CHANGE statement works in MySQL to rename a column through the various examples. Suppose we have a table named balance that contains the data as follows:

Due to some reason, we need to change the column name account_num along with its data type. In that case, we first check the structure of the table using the DESCRIBE statement as follows:

In this image, we can see that the data type of column name account_num is int. And we want to change this column name as account_no and its data type as int to varchar. Thus, we can execute the below statement to do this:

mysql> ALTER TABLE balance   

CHANGE COLUMN account_num account_no VARCHAR(25);

 

After executing the above command, we can verify it by using the DESCRIBE statement again. In the below image, the column name account_num and its data type have changed successfully.

Using the RENAME Statement:

To remove the drawback of a CHANGE statement, MySQL proposed the following syntax that illustrates the changing of the column name using a RENAME statement:

ALTER TABLE table_name   

RENAME COLUMN old_column_name TO new_column_name;

Example

Let us understand how the RENAME statement works in MySQL to change the column name through the various examples. Suppose we have a table named customer that contains the following data:

Suppose we want to change the column name account with account_no without changing its data types. We can do this by executing the below statement:

mysql> ALTER TABLE customer RENAME COLUMN account to account_no; 

After executing the above command, we can verify it by using the SELECT statement again. In the below image, the column name account has changed successfully.

Renaming Multiple Columns

MySQL also allows us to change the multiple column names within a single statement. If we want to rename multiple column names, we might use the below syntax:

ALTER TABLE table_name   

CHANGE old_column_name1 new_column_name1 Data Type,  

CHANGE old_column_name2 new_column_name2 Data Type,  

...  

...  

CHANGE old_column_nameN new_column_nameN Data Type;

  

OR

ALTER TABLE table_name   

RENAME COLUMN old_column_name1 TO new_column_name1,  

RENAME COLUMN old_column_name2 TO new_column_name2,  

...  

...  

RENAME COLUMN old_column_nameN TO new_column_nameN;

 

Example

Suppose we want to change column names id and customer_name from the customer table. To change multiple column names within a single statement, we can use the statement as follows:

mysql> ALTER TABLE customer   

CHANGE id cust_id int,   

CHANGE customer_name cust_name varchar(45);

  

After executing the above command, we can verify it by using the SELECT statement again. In the below image, the column name id and customer_name have changed successfully:

Let us again change the currently modifying column name through the RENAME COLUMN statement as follows:

mysql> ALTER TABLE customer   

RENAME COLUMN cust_id TO id,   

RENAME COLUMN cust_name TO customer_name;

After executing the above command, we can verify it by using the DESCRIBE statement again. In the below image, the column name cust_id and cust_name have changed successfully:

In this article, we have learned an introduction of the MySQL RENAME column and how to change the column name in a specified table, along with a query example for better understanding.