如何快速重命名MySQL数据库(更改模式名称)? [英] How do I quickly rename a MySQL database (change schema name)?
问题描述
MySQL 中的MySQL手册涵盖了这一点。
The MySQL manual at MySQL covers this.
通常我只是转储数据库并重新导入一个新的名称。这对于非常大的数据库不是一个选项。显然 RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
坏了
Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
does bad things, exist only in a handful of versions, and is a bad idea overall.
这需要使用 InnoDB ,它存储的东西非常不同于 MyISAM 。
This needs to work with InnoDB, which stores things very differently than MyISAM.
推荐答案
对于InnoDB,以下似乎工作:创建新的空数据库,重命名每个表到新数据库:
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:
RENAME TABLE old_db.table TO new_db.table;
之后您需要调整权限。
对于shell中的脚本,您可以使用以下任一方法:
For scripting in a shell, you can use either of the following:
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \
do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done
或
for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;
注意:选项 -p
和密码。如果您的数据库没有密码,请删除 -u username -ppassword
部分。
Notes: there is no space between the option -p
and the password. If your database has no password, remove the -u username -ppassword
part.
程序,您可以复制它们之后:
Also, if you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
这篇关于如何快速重命名MySQL数据库(更改模式名称)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!