如何快速重命名 MySQL 数据库(更改架构名称)?

2021-11-20 00:00:00 database rename mysql innodb

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;

<小时>

注意事项:

Notes:

  • 选项 -p 和密码之间没有空格.如果您的数据库没有密码，请删除 -u username -ppassword 部分.

  • 如果某个表有触发器，则无法使用上述方法将其移动到另一个数据库(将导致Trigger in wrong schema 错误).如果是这种情况，请使用传统方式克隆数据库，然后删除旧数据库:

  • There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.

  • If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db |mysql new_db

如果你有存储过程，你可以在之后复制它们:

If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db |mysql new_db

相关文章