To rename an existing column’s name in MySQL tables, you need to combine the ALTER TABLE statement with the CHANGE or RENAME COLUMN clause.
This tutorial will help you learn how to write the ALTER TABLE statement with both clauses.
For example, suppose you have the students table with the following structure:
mysql> DESCRIBE students;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | tinytext | YES | | NULL | |
| subject | tinytext | YES | | NULL | |
| score | float | YES | | NULL | |
| gender | tinytext | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
As you can see from the result set above, the students table has 5 columns (or fields).
The CHANGE clause allows you to change a table’s column name and data type with the following syntax:
ALTER TABLE [table_name]
CHANGE [current_column_name] [new_column_name] [data_type];
Suppose you want to change the id column name to student_id. The SQL statement below should work:
ALTER TABLE students CHANGE id students_id INT;
Even though we’re not changing the data type of the id column, the CHANGE clause requires you to pass the data type or it will throw an error.
Starting from MySQL version 8.0, MySQL added the RENAME COLUMN clause so that you can rename a column name without having to pass its data type as well.
The syntax for RENAME COLUMN is as shown below:
ALTER TABLE [table_name]
RENAME COLUMN [current_column_name] TO [new_column_name];
For example, let’s rename the name column to first_name:
ALTER TABLE students RENAME COLUMN name TO first_name;
When the statement above has been executed, you can run the DESCRIBE statement again to check on the renamed column:
mysql> DESCRIBE students;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| students_id | int | NO | PRI | NULL | |
| first_name | tinytext | YES | | NULL | |
| subject | tinytext | YES | | NULL | |
| score | float | YES | | NULL | |
| gender | tinytext | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
As you can see from the output above, we have succeeded in renaming the table’s column names.
When you want to rename multiple columns, you can add multiple CHANGE or RENAME COLUMN clauses separated by commas.
Take a look at the example sql statement below:
ALTER TABLE students
CHANGE id students_id INT,
CHANGE name first_name TINYTEXT,
CHANGE subject topic TINYTEXT;
-- OR
ALTER TABLE students
RENAME COLUMN id TO students_id,
RENAME COLUMN name TO first_name,
RENAME COLUMN subject TO topic;
Please keep in mind that the ALTER privilege is required for you to run the ALTER TABLE operation.
Next, let’s learn how to check and add ALTER privilege when it’s missing for your MySQL user.
Checking the ALTER privilege for your MySQL user
To check if your MySQL user already has the ALTER privilege, you need to run a SHOW GRANTS statement from the Terminal:
SHOW GRANTS FOR [user@];
For example, this is the result I got when I execute the statement for my user nathan@localhost:
mysql> SHOW GRANTS FOR nathan@localhost;
+----------------------------------------------------------------+
| Grants for nathan@localhost |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nathan`@`localhost` |
| GRANT SELECT ON `school_db`.`students` TO `nathan`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
As you can see, the user nathan@localhost only has the SELECT privilege for the students table in the school_db database.
Without the ALTER privilege, then the ALTER TABLE statement will cause MySQL to throw an ERROR 1142 as shown below:
ALTER TABLE students CHANGE id students_id INT;
ERROR 1142 (42000): ALTER command denied
to user 'nathan'@'localhost' for table 'students'
To give the ALTER privilege to the user, you need to execute the GRANT ALTER statement that has the following syntax:
GRANT privilege [,privilege],.. -- one or many privilege
ON [database.table] -- database on table
TO [username@host]; -- ex. root@localhost
Here’s an example of granting the ALTER privilege to user nathan@localhost. You need to change the username to your actual username:
GRANT ALTER ON school_db.students TO 'nathan'@'localhost';
Now run the SHOW GRANTS statement again. This time, you should see the ALTER privileges appear for your MySQL user:
mysql> SHOW GRANTS FOR nathan@localhost;
+-----------------------------------------------------------------------+
| Grants for nathan@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nathan`@`localhost` |
| GRANT SELECT, ALTER ON `school_db`.`students` TO `nathan`@`localhost` |
+-----------------------------------------------------------------------+
2 rows in set (0.01 sec)
You should now be able to run the MySQL ALTER TABLE statement with your user. Great work! 😉
