MySQL Alter Table - Rename Column

Last updated: October 19, 2026Oct 19, 2026 4 min read

Short or cryptic column names like cust_nm, ph and amt make SQL hard to read. MySQL renames columns with ALTER TABLE … RENAME COLUMN (MySQL 8.0 and later) or with the older CHANGE COLUMN. The data stays exactly as it is; only the name changes.

1. Syntax

-- MySQL 8.0+
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- any version (must repeat the full column definition)
ALTER TABLE table_name CHANGE [COLUMN] old_name new_name column_definition;

2. Renaming with RENAME COLUMN

ALTER TABLE customers RENAME COLUMN cust_nm TO customer_name;
SHOW FULL COLUMNS FROM customers;

Result: 3 rows

The type, NOT NULL, default, comment and the index all came along unchanged.

3. Renaming with CHANGE COLUMN

CHANGE needs the full definition. Watch what happens if you give only the type:

CREATE TABLE demo_change LIKE customers;
ALTER TABLE demo_change CHANGE customer_name full_name VARCHAR(60);
SHOW FULL COLUMNS FROM demo_change LIKE 'full_name';

Result: 1 row

NOT NULL, the default and the comment are gone. Restating everything keeps them:

ALTER TABLE customers CHANGE ph phone VARCHAR(15) NULL COMMENT 'mobile number';
SHOW FULL COLUMNS FROM customers LIKE 'phone';

Result: 1 row

4. Renaming Several Columns at Once

ALTER TABLE orders
  RENAME COLUMN ord_id TO order_id,
  RENAME COLUMN amt    TO amount;
SELECT * FROM orders;

Result: 3 rows

5. What Happens to Related Objects

The primary key on order_id followed the rename automatically, but the view still refers to ord_id and amt:

SELECT * FROM big_orders;

ERROR 1356 (HY000): View 'sales.big_orders' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

CREATE OR REPLACE VIEW big_orders AS SELECT order_id, amount FROM orders WHERE amount > 400;
SELECT * FROM big_orders;

Result: 2 rows

6. Common Errors

ALTER TABLE customers RENAME COLUMN customer_name TO phone;

ERROR 1060 (42S21): Duplicate column name 'phone'

ALTER TABLE customers RENAME COLUMN cust_nm TO name;

ERROR 1054 (42S22): Unknown column 'cust_nm' in 'customers'

7. Renaming Safely in a Live Application

1. Search the application code, reports and stored programs for the old name.
2. Deploy code that works with both names if possible (for example through a view), or schedule a short maintenance window.
3. Rename the column; it's an instant metadata change for InnoDB.
4. Recreate views and update routines and triggers.
5. With JPA/Hibernate, update @Column(name = "…") mappings.

8. Practice Exercises

ALTER TABLE orders RENAME COLUMN cust_id TO customer_id;
SELECT constraint_name, column_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'sales' AND table_name = 'orders' AND referenced_table_name IS NOT NULL;

ALTER TABLE customers CHANGE phone mobile VARCHAR(20) NULL COMMENT 'mobile number';
SHOW COLUMNS FROM customers LIKE 'mobile';

9. Summary

• ALTER TABLE t RENAME COLUMN old TO new; (8.0+) renames and keeps the definition.
• CHANGE old new definition renames and redefines; restate every attribute you want to keep.
• Rename several columns in one ALTER TABLE.
• Indexes, foreign keys and checks follow; views, routines and application code don't.