How to Rename a Column in MySQL

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.

Prerequisites

• MySQL installed.
• A user with ALTER privileges on the MySQL server.

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.

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;

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

ALTER TABLE employees RENAME COLUMN id TO employee_id;

Confirm the change with:

SELECT * FROM employees;

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.

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;

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);

Confirm the change with:

DESCRIBE employees;

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;

Verify the changes with the following command:

SELECT * FROM employees;

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;

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

DESCRIBE employees;

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.

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

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

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

6. Click the Change link.

7. Type in the new column name.

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

Once completed, myPhpAdmin prints a 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.