5

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?

Suanmeiguo
  • 1,365
  • 3
  • 17
  • 28

1 Answers1

8

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.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • so, does it mean 'alter table' is faster? and also what is a TEMPORARY table? – Suanmeiguo Sep 25 '13 at 01:00
  • 2
    @Suanmeiguo: No, there should be no difference in speed. As documented under [`CREATE TABLE` Syntax](http://dev.mysql.com/doc/en/create-table.html): "*A `TEMPORARY` table is visible only to the current connection, and is dropped automatically when the connection is closed.*" – eggyal Sep 25 '13 at 03:52
  • 1
    Thank you eggyal. Here's more detail about temporarytable. http://dev.mysql.com/doc/refman/5.0/en/create-table.html – Suanmeiguo Sep 30 '13 at 16:31