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.
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;
table_nameis the name where your target column resides.
old_column_namerepresents the current name you wish to rename while the
new_column_nameis the name you wish to assign to the column.
- Finally, the
column_typeparameter sets the data type of the target column.
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
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.
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.