[mysql] 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

Syntax: ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

If table name is Student and column name is Name. Then, if you want to change Name to First_Name

ALTER TABLE Student CHANGE Name First_Name varchar(20);

Rename MySQL Column with ALTER TABLE Command

ALTER TABLE is an essential command used to change the structure of a MySQL table. You can use it to add or delete columns, change the type of data within the columns, and even rename entire databases. The function that concerns us the most is how to utilize ALTER TABLE to rename a column.

Clauses give us additional control over the renaming process. The RENAME COLUMN and CHANGE clause both allow for the names of existing columns to be altered. The difference is that the CHANGE clause can also be used to alter the data types of a column. The commands are straightforward, and you may use the clause that fits your requirements best.

How to Use the RENAME COLUMN Clause (MySQL 8.0)

The simplest way to rename a column is to use the ALTER TABLE command with the RENAME COLUMN clause. This clause is available since MySQL version 8.0.

Let’s illustrate its simple syntax. To change a column name, enter the following statement in your MySQL shell:

ALTER TABLE your_table_name RENAME COLUMN original_column_name TO new_column_name;

Exchange the your_table_name, original_column_name, and new_column_name with your table and column names. Keep in mind that you cannot rename a column to a name that already exists in the table.

Note: The word COLUMN is obligatory for the ALTER TABLE RENAME COLUMN command. ALTER TABLE RENAME is the existing syntax to rename the entire table.

The RENAME COLUMN clause can only be used to rename a column. If you need additional functions, such as changing the data definition, or position of a column, you need to use the CHANGE clause instead.

Rename MySQL Column with CHANGE Clause

The CHANGE clause offers important additions to the renaming process. It can be used to rename a column and change the data type of that column with the same command.

Enter the following command in your MySQL client shell to change the name of the column and its definition:

ALTER TABLE your_table_name CHANGE original_column_name new_col_name data_type;

The data_type element is mandatory, even if you want to keep the existing datatype.

Use additional options to further manipulate table columns. The CHANGE also allows you to place the column in a different position in the table by using the optional FIRST | AFTER column_name clause. For example:

ALTER TABLE your_table_name CHANGE original_column_name new_col_name y_data_type AFTER column_x;

You have successfully changed the name of the column, changed the data type to y_data_type, and positioned the column after column_x.

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);

In Server version: 5.6.34 MySQL Community Server

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type;

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

For MySQL 8

alter table creditReportXml_temp change column applicationID applicantID int(11);

for mysql version 5

alter table *table_name* change column *old_column_name* *new_column_name* datatype();

Rename column name in mysql

alter table categories change  type  category_type varchar(255);

You can use following code:

ALTER TABLE `dbName`.`tableName` CHANGE COLUMN `old_columnName` `new_columnName` VARCHAR(45) NULL DEFAULT NULL ;

In mysql your query should be like

ALTER TABLE table_name change column_1 column_2 Data_Type;

you have written the query in Oracle.

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