How To Rename a Table Column in MySQL Database

Table columns are some of the most common and useful objects we interact with when working with MySQL databases. In some cases, you may encounter scenarios where you need to modify the name of an existing table column.

Whether you are avoiding name collision or fixing a typo, renaming a table column is one of the most common operation as a database adminstator.

In this tutorial, we will cover the various method of renaming a table column in MySQL Server. Whether you are using MySQL 5.0 or MySQL 8.0 and above. We have you covered.

Let us jump in!

NOTE: Depending on your database configuration, the methods discussed in this tutorial require the ALTER privilege on the target table.

MySQL 5.0 Rename Column

If you are running MySQL 5.0 and below, you can use the ALTER TABLE followed by the CHANGE COLUMN statement. The syntax for this statement is as shown below:

ALTER TABLE table_name 
CHANGE COLUMN old_column_name new_column_name column_type;

Where the

1. table_name is the name where your target column resides.
2. The old_column_name represents the current name you wish to rename while the new_column_name is the name you wish to assign to the column.
3. Finally, the column_type parameter sets the data type of the target column.

Example

For example, suppose we have a table that stores user information with the column layout as shown:

id int auto_increment primary key,
first_var varchar(100),
email varchar(255) not null,
active int not null

Suppose we wish to rename the active column to is_active. We can run the query as shown:

ALTER TABLE users 
CHANGE COLUMN active is_active int;

This should rename the specified column from active to is_active.

MySQL 8.0 and Above Rename Column.

As of MySQL 8.0, we can use the RENAME COLUMN clause in the ALTER TABLE statement to rename a column.

The statement syntax is as shown

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

For example, to rename the active column from the previous example to the is_active using the RENAME COLUMNstatement, we can run the query as shown:

ALTER TABLE users
RENAME COLUMN active TO is_active;

In this case, we do not need to specify the data type of the column which minimizes the potential for any errors and ensures that the data type is preserved across the database.

Note: In both methods, you must update your SQL queries, stored procedures, or applications that reference the old column name, as MySQL will not automatically reflect the new name.

Conclusion

In this tutorial, we learned how we can rename an existing table column in MySQL using two main methods. Whether you are using the latest MySQL version or an older version, we have you covered.

We hope you enjoyed this post, consider subscribing to get the latest articles as they go live.