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.
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;
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
