How to Rename a Column in MySQL

May 15, 2024

Introduction

Renaming a column in MySQL is important for database maintenance and data organization. It allows you to update column names to reflect their purpose better and maintain consistency and clarity in MySQL database structures.

The following text explains how to rename a column in MySQL using different methods.

how to rename a colum in mysql

Prerequisites

Rename MySQL Column with ALTER TABLE Command

ALTER TABLE is an essential command used to change the MySQL table structure. Use it to add or delete columns, change the data type within columns, and rename entire databases. Another way to rename a column is with ALTER TABLE.

To get additional control over the renaming process, use statements. The RENAME COLUMN and CHANGE statements allow for the column names to be altered. The difference is that the CHANGE clause also alters the column data type.

Rename MySQL Column with the RENAME Statement

The simplest way to rename a column is to use the ALTER TABLE command with the RENAME COLUMN clause. This clause has been available since MySQL version 8.0.

Note: To rename a column in MySQL 5.7.x with ALTER TABLE, run the following command: ALTER TABLE [table_name] CHANGE [old_column_name] [new_column_name] [column definition];
Make sure to restate the full column definition to avoid undeclared attributes reverting to default values.

To change a column name, enter the following statement in the MySQL shell:

ALTER TABLE [table_name] RENAME COLUMN [old_column_name] TO [new_column_name];

Replace [table_name], [old_column_name], and [new_column_name] with your table and column names. Keep in mind that you cannot rename a column to a name that already exists in the table.

For example, the table employees has five columns. To view the entire table, including the data, run:

SELECT * FROM employees;
select from terminal output

To change the column id into employee_id in the table employees, run:

ALTER TABLE employees RENAME COLUMN id TO employee_id;
mysql ALTER TABLE RENAME COLUMN terminal output

Confirm the change with:

SELECT * FROM employees;
mysql SELECT FROM terminal output

The RENAME COLUMN statement is used only to rename a column. If you need additional functions, such as changing the data type, use the CHANGE clause instead.

Note: The keyword COLUMN is obligatory for the ALTER TABLE RENAME COLUMN command to avoid changing the name of the whole table.

Rename MySQL Column with the CHANGE Statement

The CHANGE clause renames a column and changes its data type. Use the following syntax to change the name of the column and its data type:

ALTER TABLE [table_name] CHANGE [old_column_name] [new_col_name] Data Type;

You can change the column data type or keep the existing one. In both cases, you have to specify the data type as the element is mandatory. To verify the data type, check the column structure with:

DESCRIBE employees;
mysql DESCRIBE terminal output

For example, the field employee-id has the int type. To change the column name back to id and the type to VARCHAR(25), execute the following:

ALTER TABLE employees CHANGE employee_id id VARCHAR(25);
ALTER TABLE CHANGE statement terminal output

Confirm the change with:

DESCRIBE employees;
mysql DESCRIBE terminal output changed column type and name

How to Rename Multiple Columns in MySQL

MySQL allows you to rename multiple columns with a single command. This option is possible with the RENAME COLUMN command and the CHANGE statement.

To change the multiple column names using the RENAME COLUMN clause, use the syntax:

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 example, to change the names of the following columns:

  • id to employee_id.
  • department to sector.
  • salary to payment.

Run the following command:

ALTER TABLE employees
RENAME COLUMN id TO employee_id,
RENAME COLUMN department TO sector,
RENAME COLUMN salary TO payment;
rename multiple columns with ALTER RENAME terminal output

Verify the changes with the following command:

SELECT * FROM employees;
SELECT FROM command terminal output

To change the names of multiple columns using the CHANGE clause, use the syntax:

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;

For example, to change back the names of the columns used above, run the following command:

ALTER TABLE employees
CHANGE employee_id id int,
CHANGE sector department varchar (25),
CHANGE payment salary int;
ALTER TABLE CHANGE statement terminal output for multiple columns

Run the following to verify the changes in name and type:

DESCRIBE employees;
DESCRIBE command terminal output

Rename MySQL Column via PhpMyAdmin

Another way to rename a column is via phpMyAdmin. To accomplish that, take the following steps:

1. Open phpMyAdmin in your web browser.

2. Select the database with the table and the column you want to rename. For example, mydatabase.

phpMyAdmin select database

3. Select the table. In this example, it's test_table.

phpMyAdmin select table

4. Click the Structure tab at the top of the page to view the table structure.

table structure tab myPhpAdmin

5. Find the column you want to rename in the list of columns. For instance, the id column.

6. Click the Change link.

click the change link

7. Type in the new column name.

table column name change

8. At the bottom of the page, click the Save button.

Once completed, myPhpAdmin prints a confirmation message.

Column name change confirmation message

Conclusion

This article showed several different methods to rename a column in MySQL using practical examples.

Next, learn how to back up and restore a MySQL database.

Was this article helpful?
YesNo
Sara Zivanov
Sara Zivanov is a technical writer at phoenixNAP who is passionate about making high-tech concepts accessible to everyone. Her experience as a content writer and her background in Engineering and Project Management allows her to streamline complex processes and make them user-friendly through her content.
Next you should read
How to Allow Remote Connections to MySQL
July 4, 2024

Keep your database servers separate from your public-facing web servers to improve security, performance, and...
Read more
How To Find Duplicate Values in MySQL
July 25, 2024

Learn how to find the duplicate entries in your MySQL databases. The guide shows you how to use the GROUP BY...
Read more
How to Improve MySQL Performance With Tuning
April 25, 2024

The performance of MySQL databases is an essential factor in the optimal operation of your server. Make sure...
Read more
How to Fix MySQL "Command Not Found" (Linux, Windows, mac OS)
April 11, 2024

The 'Command Not Found' error is a general error not only found in MYSQL. By learning how to deal with it...
Read more