How to Change a Column Name in MySQL

MySQL 5.6.x and 5.7.x

Renaming a column in MySQL involves using the ALTER TABLE command. For MySQL version 5.6 .x and 5.7.x, the typical syntax is as follows:

ALTER TABLE table_name CHANGE old_column_name new_column_name <column definition>;

ALTER TABLE products CHANGE product_name product_full_name VARCHAR(100) NOT NULL;

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default. For example, not stating NOT NULL will result in the column allowing NULLS.

To ensure that you do not miss anything, you can use the SHOW CREATE TABLE command to see the full column definition:

mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) NOT NULL,

(The rest of the output is truncated for brevity)

Then use that as basis for the ALTER TABLE command.

MySQL 8.0

While MySQL 8.0 accepts the above syntax, it also support an easier way:

ALTER TABLE products RENAME COLUMN product_name TO product_full_name;

This is a lot easier since there's no longer a need to restate the full column definition. But if you need to change both the column name and something in the definition, you can use the ALTER TABLE ... CHANGE command to do it in one go.