Rename the column name in MySQL / MariaDB

Rename the column name done with ALTER command with the CHANGE clause. Use the CHANGE clause to rename the column and If a column has an index, then it’s also updated.

Syntax:

ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name column_definition [FIRST|AFTER col_name]

Example:

DESC EMPLOYEES;

Field         |Type        |Null|Key|Default|Extra|
--------------+------------+----+---+-------+-----+
employeeNumber|int(11)     |NO  |PRI|       |     |
lastName      |varchar(50) |NO  |   |       |     |
firstName     |varchar(50) |NO  |   |       |     |
extension     |varchar(10) |NO  |   |       |     |
email         |varchar(100)|NO  |   |       |     |
officeCode    |varchar(10) |NO  |MUL|       |     |
reportsTo     |int(11)     |YES |MUL|       |     |
jobTitle      |varchar(50) |NO  |   |       |     |

Change the column office code to officecode_new and modify its datatype also with varchar(100).

alter table employees change column officecode officecode_new varchar(200);

Describe the employee table again and check the index on the table on the column is also modified with a new name

Field         |Type        |Null|Key|Default|Extra|
--------------+------------+----+---+-------+-----+
employeeNumber|int(11)     |NO  |PRI|       |     |
lastName      |varchar(50) |NO  |   |       |     |
firstName     |varchar(50) |NO  |   |       |     |
extension     |varchar(10) |NO  |   |       |     |
email         |varchar(100)|NO  |   |       |     |
officecode_new|varchar(200)|YES |MUL|       |     |
reportsTo     |int(11)     |YES |MUL|       |     |
jobTitle      |varchar(50) |NO  |   |       |     |

Note: on Checking its index is also updated to a new column name i.e officecode_new.