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! 😉