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:
- Using the CHANGE statement
- Using the RENAME statement
Using the CHANGE Statement:
The following are the syntax that illustrates the column rename using the CHANGE statement:
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:
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:
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:
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:
OR
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:
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:
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.