Rename a column in MySQL

I am trying to rename a column in MySQL community server 5.5.27 using this SQL expression:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

I also tried

ALTER TABLE table_name RENAME old_col_name TO new_col_name;

But it says:

Error: check the Manual that corresponds to your MySQL server version

Solution 1:

Use the following query:

ALTER TABLE tableName CHANGE oldcolname newcolname datatype(length);

The RENAME function is used in Oracle databases.

ALTER TABLE tableName RENAME COLUMN oldcolname TO newcolname datatype(length);

@lad2025 mentions it below, but I thought it'd be nice to add what he said. Thank you @lad2025!

You can use the RENAME COLUMN in MySQL 8.0 to rename any column you need renamed.

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

ALTER TABLE Syntax: RENAME COLUMN:

• Can change a column name but not its definition.
• More convenient than CHANGE to rename a column without changing its definition.

Solution 2:

In Server version: 5.6.34 MySQL Community Server

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type;

Solution 3:

From MySQL 5.7 Reference Manual.

Syntax :

ALTER TABLE t1 CHANGE a b DATATYPE;

e.g. : for Customer TABLE having COLUMN customer_name, customer_street, customercity.

And we want to change customercity TO customer_city :

alter table customer change customercity customer_city VARCHAR(225);

Solution 4:

From MySQL 8.0 you could use

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;

ALTER TABLE Syntax:

RENAME COLUMN:

• Can change a column name but not its definition.

• More convenient than CHANGE to rename a column without changing its definition.

DBFiddle Demo