mysql - 如何快速重命名MySQL数据库（更改模式名称）？(How do I quickly rename a MySQL database (change schema name)?)

Question

The MySQL manual at MySQL covers this.

(在MySQL手册的MySQL涵盖这一点。)

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;

(显然是RENAME {DATABASE | SCHEMA} db_name TO new_db_name;) 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 .

(做坏事，只存在于少数几个版本中，总体上是一个坏主意 。)

This needs to work with InnoDB , which stores things very differently than MyISAM .

(这需要与InnoDB一起使用， InnoDB的存储方式与MyISAM完全不同。)

  ask by community wiki translate from so

Answer 1

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

(对于InnoDB，以下似乎可行：创建新的空数据库，然后依次将每个表重命名为新数据库：)

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

(之后您需要调整权限。)

For scripting in a shell, you can use either of the following:

(对于shell中的脚本，您可以使用以下任一方法：)

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

Or

(要么)

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:

(笔记：)

• There is no space between the option -p and the password.

  (选项-p和密码之间没有空格。)

  If your database has no password, remove the -u username -ppassword part.

  (如果您的数据库没有密码，请删除-u username -ppassword部分。)

• If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error).

  (如果某个表具有触发器，则无法使用上述方法将其移动到另一个数据库（将导致Trigger in wrong schema错误中的Trigger in wrong schema ）。)

  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