Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 306
Suanmeiguo Avatar asked Sep 24 '13 18:09

Suanmeiguo


People also ask

What is the difference between alter and rename in SQL?

Summary. The alter command is used when we want to modify a database or any object contained in the database. The drop command is used to delete databases from MySQL server or objects within a database. The rename command is used to change the name of a table to a new table name.

What is meant by renaming a table?

In some situations, database administrators and users want to change the name of the table in the SQL database because they want to give a more relevant name to the table. Any database user can easily change the name by using the RENAME TABLE and ALTER TABLE statement in Structured Query Language.


1 Answers

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.

like image 197
eggyal Avatar answered Oct 07 '22 01:10

eggyal