rename table mysql_mysql rename table Syntax

This statement renames one or more tables.

可以同时rename多个table。

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):

CREATE TABLE new_table (...);

RENAME TABLE old_table TO backup_table, new_table TO old_table;上面这个我觉得挺不错的，今天也正好用到了。

If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that tmp_table does not already exist):

多个table，rename为从左至右的顺序。RENAME TABLE old_table TO tmp_table,

new_table TO old_table,

tmp_table TO new_table;

As long as two databases are on the same file system, you can useRENAME TABLEto move a table from one database to another:

可以改到另一个database，前提是在同一个文件系统上，这个一般都会满足吧。RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

If there are any triggers associated with a table which is moved to  a different database  usingRENAME TABLE, then the statement fails with the error Trigger in wrong schema.

有trigger，还要改database，那就会报错了，Trigger in wrong schema。

RENAME TABLEalso works for views, as long as you  do not try to rename a view into a different database.

rename table 可以改变view的名字，但是不能rename到另一个数据库。

Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually.

对于table或view上特别指定的权限就不能自动转移啦，只能手动修改了

。

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have theALTERandDROPprivileges on the original table, and theCREATEandINSERTprivileges on the new table.

rename不能被锁住表，或者有active transactions。旧表你要有ALTER and DROP权限，然后需要CREATE and INSERT来对新表进行操作

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.

多个表rename时出错的话，所有的表都会回到原先状态。

Youcannot use RENAME to rename a TEMPORARY  table. However, you can useALTER TABLEinstead:

mysql> ALTER TABLE orig_name RENAME new_name;不能rename临时表，不过可以用alter talbe来修改。