How RENAME statement works actually? [closed]

Question

To rename any table in SQL we use following command:

RENAME OldTableName TO NewTableName

How does this work internally?

My assumption is it probably first creates a table with NewTableName using AS SELECT, then deletes the old table with DROP, just like we did when renaming files when handling files in C. Am I right, or does it work differently?

Answer 1

It works differently. The data isn't copied. The name of the table is simply updated in the metadata tables -- along with references to things like triggers, and so on.

Answer 2

You may get some help from here:-

The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows (assuming that backup_table does not already exist). ...............

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed tables to return everything to its original state.