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?
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.
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.
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 tabletbl_name
without making a copy. (You can also use theRENAME 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
, unlikeALTER 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 forTEMPORARY
tables. However, you can useALTER 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With