MySQL Rename Column

Last updated on July 26th, 2024 at 07:49 am

Sometimes you may need to change column name in MySQL or rename column name without changing its type. Here’s how to rename column in MySQL query.

Table of Contents

How To Change Column Name in MySQL

Here are the steps to change column name in MySQL query using MySQL ALTER TABLE statement.

Rename Column in MySQL 5.6.x and 5.7.x

Here’s the SQL query to rename column in MySQL.

ALTER TABLE table_name CHANGE old_column_name new_column_name <column definition>;

In the above query, you need to mention table_name after ALTER TABLE, old_column_name and new_column_name after CHANGE keyword. You also need to mention the entire column definition of your column, even if there is no change in it. Otherwise, MySQL will change undeclared attributes to default value.

For example, if you have a NOT NULL constraint on your MySQL column but do not mention it while renaming the column, then MySQL will drop that constraint and allow null values for that column, after renaming it.

Bonus Read : MySQL Stored Procedure with Parameters

Here’s a rename column example. Let’s say you have the following table orders

mysql> describe orders;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| product_name | varchar(255) | NO   |     | NULL              |                |
| order_date   | date         | YES  |     | NULL              |                |
| price        | int(11)      | NO   |     | NULL              |                |
| description  | text         | YES  |     | NULL              |                |
| created_at   | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+--------------+------+-----+-------------------+----------------+

mysql> alter table orders change price item_price int(11);

mysql> describe orders;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| product_name | varchar(255) | NO   |     | NULL              |                |
| order_date   | date         | YES  |     | NULL              |                |
| item_price   | int(11)      | YES  |     | NULL              |                |
| description  | text         | YES  |     | NULL              |                |
| created_at   | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+--------------+------+-----+-------------------+----------------+

Bonus Read : How to Duplicate Table in MySQL

Rename Column in MySQL 8.0

Since MySQL 8.0, there is another easier way to rename column without changing type (without specifying entire column definition) using RENAME COLUMN clause.

ALTER TABLE products RENAME COLUMN product_name TO product_full_name;

Please note, ALTER TABLE statement is also used to change table name in MySQL. But this has a different syntax and gives a completely different result.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How To Get Last Record In Each Group In MySQL

How to Prevent SQL Injection Attack

How To Execute Stored Procedure In MySQL Workbench