Rename a column in MySQL

Question

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

Renshaw

possible duplicate of How to rename a table column in MySQL – Seasickness

Answer 1

Use this 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.

Sixtieth

Can you have columns with the same name - or why does the datatype it was have to be specified? – Lakenyalaker

@Lakenyalaker As the command says, CHANGE changes the column on a table and its type. The MYSQL docs state: Attributes present in the original definition but not specified for the new definition are not carried forward.. Meaning you have to specify the table definitions or they will not be used for that column. If you only want to change a column's datatype, simply leave out the newcolname – Sixtieth

@Flimm you're right again. Oracle docs; In MySQL, the quote character is the backtick. If the SQL mode ANSI_QUOTES is set, double quotes can also be used to quote the identifiers. In Oracle, identifiers are quoted using double quotation marks. Aaaand edited. I need coffee.. – Sixtieth

@JorgeMachado It works just fine. I believe it is not my example that is creating errors for you. Check the syntax, make sure you are using the right example (Top for MySQL, bottom for OracleDB). – Sixtieth

I tried it on my mysql-8.0.11-winx64 and it did not work. Worked when I left the quotes out completly: .....-> ALTER TABLE tableName CHANGE oldcolname newcolname datatype(length); – Dube

Thank you @Smogen! That's odd. Backticks are supposed to be the default quote character for MySQL. Are you sure you didn't use " instead of `? – Sixtieth

@RizkyFakkel absolutely sure. Maybe its OS dependent? – Dube

I agree, but I'm assuming renaming a column doesn't happen often in bigger applications, and if they do they are probably tested on a test environment beforehand. Maybe MariaDB will come with an equivalent of RENAME COLUMN x TO y in the future. – Sixtieth

In MySQL (MYSQL STATUS shows mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper), I get Error 1064 (42000): You have an error in your SQL Syntax;...near 'COLUMN thread_id TO threadId'... for the query ALTER TABLE comment RENAME COLUMN thread_id TO threadId;. I also tried adding backticks `. I ended up using CHANGE. – Mercurous

Does this operation delete constraints on the column? EDIT: after checking information_schema.key_column_usage, it seems the column name in constraints is updated with this operation. – Cinchonine

Back ticks are not needed usually in MySQL unless the table or column name or function name, etc. is a reserved word or illegal names [like with special characters or embedded spaces. The back tick is also like [] square brackets in SQL Server are used in like manner to allow for names that are not normally legal or a reserved word. – Ungley

Your answer seems slightly misleading. It wasn't working for me until I reads further down to another answer where someone else had done the same but without the quotation marks. Removing them worked for me. – Externalism

Just a quick heads-up - you need to include the entire "column definition", not just datatype(length) (docs for ALTER TABLE). – Intramundane

Which form is standard SQL? – Pimp

I hate that you have to provide the full column definition to "rename" a column. Wish I could create an alias or something that does this for me because I always forget. – Irradiant

Answer 2

In Server version: 5.6.34 MySQL Community Server

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type;

Chemosynthesis

absolutely dont use DROP and ADD. you will lose all data by doing this – Scrivenor

This answer doesn't add much value to the accepted answer. – Companionate

Just a quick heads-up - you need to include the entire "column definition", not just datatype (docs for ALTER TABLE). – Intramundane

This helped me as the additional 'TO' in other solutions is what was stopping me. Once I removed it, all was well. – Radiculitis

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

Thursby

Answer 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

Halfmast

Answer 5

ALTER TABLE `table_name` CHANGE `$old_column_name` `new_column_name` VARCHAR(40)

this is working for me

Interplead

Please, add an explanation. See how to answer. – Apostil

Answer 6

You can use following code:

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

Baeza

This answer doesn't add much value to the accepted answer. – Companionate

Answer 7

Changing name in MySQL we have to use "ALTER" table command followed by "CHANGE". Below is the query.

ALTER TABLE tablename CHANGE COLUMN oldcolname newcolname datatype;

ALTER TABLE tablename CHANGE oldcolname newcolname datatype;

PS- You can add "COLUMN" word or ignore in the query. It will work same.

"RENAME" is used in Oracle database.

Cheerio

Answer 8

Remember the backticks

Something that threw me off and that I'm mentioning here explicitly since no other answer seems to - column names need to be surrounded by backticks, of all things. SQL is the only language I've ever come across that insists on backticks for quoting, and the pseudocode of the other answers don't even make it clear that quoting is needed at all.

For MySQL 8 onwards:

ALTER TABLE table_name RENAME COLUMN `old_col_name` TO `new_col_name`;

For MySQL 7 and earlier:

ALTER TABLE table_name CHANGE `old_col_name` `new_col_name` datatype(length);

Schoening

I like this answer because I can quickly see which commands work for which MySQL versions – Cosmopolite

Answer 9

Rename column name in mysql

alter table categories change  type  category_type varchar(255);

Achromatin

This answer doesn't add much value to the accepted answer. – Companionate

Answer 10

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.

Cantara

This answer doesn't add much value to the accepted answer. – Companionate

Answer 11

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

Butterworth

This answer doesn't add much value to the accepted answer. – Companionate

Answer 12

ALTER TABLE table_name CHANGE old_column_name new_column_name columnDataType;

Moslemism

This appears to be just a repeat of many of the existing answers. – Doggone

Answer 13

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

Charlyncharm

This answer doesn't add much value to the accepted answer. – Companionate

Answer 14

for mysql version 5

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

Shirtmaker

This answer doesn't add much value to the accepted answer. – Companionate

Answer 15

For MySQL <= 8

ALTER TABLE table_names CHANGE `old_column_name` `new_column_name` varchar(50);

Sanguine

How does this answer differ from mine? – Moslemism

I did not get an answer.but I with (`) got the answer – Sanguine

it's not working for me and is through an error. – Into

This appears to be just a repeat of many of the existing answers. – Doggone

Answer 16

Be careful of most of the answers above. I lost my default value following the accepted answer.

If your column has a default value or other non-default values the correct syntax for MySql 5 is:

ALTER TABLE table_name CHANGE col1 col2 column_definition;

From the Manual:
For column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change only INT to BIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;
That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

Cense

@Intramundane I thought that your comment on a couple of posts should be an answer, since I got burned without it. – Cense

Answer 17

None of the above worked when I had a column with parenthesis.

Then I tried ` and the magic worked. So if you have a special character in your column by mistake and you want to rename it, use ` for the name of the existing column. For example:

ALTER TABLE table_name RENAME COLUMN column(old) TO new_column;

Scotsman

Answer 18

Posting it here, it helps helps else ignore it but when trying to use the Change Column and Rename column functions it is throwing me an error. So figured I would see what statement is generated when we go ahead and rename the column by going into table properties. Below is the command been generated.

EXEC DB.sys.sp_rename N'db.tablename.TrackingIDChargeDescription1' , N'ChargeDescription1', 'COLUMN';

I used and renamed bunch of columns in table.

Daisy