To rename an existing MySQL table in your database, you can use either the
RENAME TABLE statement or the
ALTER TABLE statement.
The syntax for renaming a table the
ALTER TABLE statement is as follows:
ALTER TABLE [old_table_name] RENAME [new_table_name];
You need to replace
[old_table_name] with an existing table name and
[new_table_name] with the new name that you desire.
When using the
RENAME TABLE statement, the syntax is as follows:
RENAME TABLE [old_table_name] TO [new_table_name]
The difference between
ALTER TABLE and
RENAME TABLE is that the
RENAME TABLE statement allows you to rename multiple tables in one statement.
Take a look at the example below of how
RENAME TABLE can rename multiple tables at once:
RENAME TABLE [old_table_name] TO [new_table_name], [old_table_name] TO [new_table_name], [old_table_name] TO [new_table_name], [old_table_name] TO [new_table_name];
ALTER TABLE statement can only change one table name at a time:
ALTER TABLE [old_table_name] RENAME [new_table_name]; ALTER TABLE [old_table_name] RENAME [new_table_name]; ALTER TABLE [old_table_name] RENAME [new_table_name];
Note that you need the
INSERT privileges for all the tables that you want to rename.
Here’s an example of how to grant those privileges to your user:
GRANT ALTER, DROP, CREATE, INSERT ON school_db TO 'nathan'@'localhost';
The above statement will grant the privileges to user
nathan for the
school_db database only. You need to change the database and user names above to your actual names.
Finally, if you encounter an ERROR 1064 when renaming your table, you may have a table name that’s reserved as a MySQL keyword.
For example, I have a table named
distinct that I want to rename, but the following statement causes an error:
mysql> RENAME TABLE distinct TO users; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct TO users' at line 1
The error above is because MySQL thought I want to execute the
To fix the error, and avoid colliding with MySQL reserved keywords, the table name must be enclosed in backticks as shown below:
RENAME TABLE `distinct` TO users;
It’s a good practice to always enclose table names with backticks in your SQL statements, so I recommend you always do it when you write table names in your MySQL statements.
RENAME TABLE `distinct` TO `users`;
And that’s how you can rename tables in your MySQL database server. Nice work! 😉