Search code examples
mysqlalter-tabletable-rename

what is the difference between 'alter table rename' and 'rename table'?

I am using MySQL. Here's an example, I want to rename a table A to B, so what's the difference between following statement:

alter table A rename to B;

and this one:

rename table A to B;

Can anyone give a detail compare between them? Is it vary based on different engine?

Solution

  • As documented under ALTER TABLE Syntax:

    For ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.32, “RENAME TABLE Syntax”.) Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

    As documented under RENAME TABLE Syntax:

    RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a single statement:

    RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

    [ deletia ]

    RENAME TABLE does not work for TEMPORARY tables. However, you can use ALTER TABLE to rename temporary tables.

    RENAME TABLE works for views, except that views cannot be renamed into a different database.

    There are no other differences.