如何快速重命名 MySQL 数据库（更改架构名称）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/67093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I quickly rename a MySQL database (change schema name)?
提问by deadprogrammer
The MySQL manual at MySQLcovers 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;does bad things, exist only in a handful of versions, and is a bad idea overall.
通常我只是转储数据库并使用新名称重新导入它。对于非常大的数据库，这不是一个选项。显然RENAME {DATABASE | SCHEMA} db_name TO new_db_name;做了坏事，只存在于少数版本中，总体上是个坏主意。
This needs to work with InnoDB, which stores things very differently than MyISAM.
这需要与InnoDB 一起使用，它存储的东西与MyISAM非常不同。
采纳答案by Thorsten
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
-pand the password. If your database has no password, remove the-u username -ppasswordpart. If some table has a trigger, it cannot be moved to another database using above method (will result
Trigger in wrong schemaerror). If that is the case, use a traditional way to clone a database and then drop the old one:mysqldump old_db | mysql new_dbIf you have stored procedures, you can copy them afterwards:
mysqldump -R old_db | mysql new_db
- 选项
-p和密码之间没有空格。如果您的数据库没有密码，请删除该-u username -ppassword部分。 如果某个表有触发器，则无法使用上述方法将其移动到另一个数据库（会导致
Trigger in wrong schema错误）。如果是这种情况，请使用传统方式克隆数据库，然后删除旧数据库：mysqldump old_db | mysql new_db如果你有存储过程，你可以在之后复制它们：
mysqldump -R old_db | mysql new_db
回答by hendrasaputra
Use these few simple commands:
使用这几个简单的命令：
mysqldump -u username -p -v olddatabase > olddbdump.sql
mysqladmin -u username -p create newdatabase
mysql -u username -p newdatabase < olddbdump.sql
Or to reduce I/O use the following as suggested by @Pablo Marin-Garcia:
或者按照@Pablo Marin-Garcia 的建议减少 I/O 使用以下内容：
mysqladmin -u username -p create newdatabase
mysqldump -u username -v olddatabase -p | mysql -u username -p -D newdatabase
回答by raphie
I think the solution is simpler and was suggested by some developers. phpMyAdmin has an operation for this.
我认为解决方案更简单，并且是一些开发人员建议的。phpMyAdmin 对此有一个操作。
From phpMyAdmin, select the database you want to select. In the tabs there's one called Operations, go to the rename section. That's all.
从 phpMyAdmin 中，选择要选择的数据库。在选项卡中有一个称为操作，转到重命名部分。就这样。
It does, as many suggested, create a new database with the new name, dump all tables of the old database into the new database and drop the old database.
正如许多人所建议的那样，它确实使用新名称创建了一个新数据库，将旧数据库的所有表转储到新数据库中并删除旧数据库。
回答by ErichBSchulz
You can use SQL to generate an SQL script to transfer each table in your source database to the destination database.
您可以使用 SQL 生成 SQL 脚本，将源数据库中的每个表传输到目标数据库。
You must create the destination database before running the script generated from the command.
在运行从命令生成的脚本之前，您必须创建目标数据库。
You can use either of these two scripts (I originally suggested the former and someone "improved" my answer to use GROUP_CONCAT. Take your pick, but I prefer the original):
您可以使用这两个脚本中的任何一个（我最初建议使用前者，有人“改进”了我的答案以使用GROUP_CONCAT. 选择，但我更喜欢原版）：
SELECT CONCAT('RENAME TABLE .', table_name, ' TO .', table_name, '; ')
FROM information_schema.TABLES
WHERE table_schema='';
or
或者
SELECT GROUP_CONCAT('RENAME TABLE .', table_name, ' TO .', table_name SEPARATOR '; ')
FROM information_schema.TABLES
WHERE table_schema='';
($1 and $2 are source and target respectively)
（$1 和 $2 分别是源和目标）
This will generate a SQL command that you'll have to then run.
这将生成一个 SQL 命令，然后您必须运行该命令。
Note that GROUP_CONCAThas a default length limit that may be exceeded for databases with a large number of tables. You can alter that limit by running SET SESSION group_concat_max_len = 100000000;(or some other large number).
请注意，GROUP_CONCAT具有大量表的数据库可能会超出默认长度限制。您可以通过运行SET SESSION group_concat_max_len = 100000000;（或其他一些大数字）来更改该限制。
回答by Marciano
Emulating the missing RENAME DATABASEcommand in MySQL:
RENAME DATABASE在 MySQL 中模拟丢失的命令：
- Create a new database
Create the rename queries with:
SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name, ' TO ','new_schema.',table_name,';') FROM information_schema.TABLES WHERE table_schema LIKE 'old_schema';Run that output
- Delete old database
- 创建一个新的数据库
使用以下命令创建重命名查询：
SELECT CONCAT('RENAME TABLE ',table_schema,'.',table_name, ' TO ','new_schema.',table_name,';') FROM information_schema.TABLES WHERE table_schema LIKE 'old_schema';运行该输出
- 删除旧数据库
It was taken from Emulating The Missing RENAME DATABASE Command in MySQL.
回答by longneck
Three options:
三个选项：
Create the new database, bring down the server, move the files from one database folder to the other, and restart the server. Note that this will only work if ALL of your tables are MyISAM.
Create the new database, use CREATE TABLE ... LIKE statements, and then use INSERT ... SELECT * FROM statements.
Use mysqldump and reload with that file.
创建新数据库，关闭服务器，将文件从一个数据库文件夹移动到另一个，然后重新启动服务器。请注意，这仅在您的所有表都是 MyISAM 时才有效。
创建新数据库，使用 CREATE TABLE ... LIKE 语句，然后使用 INSERT ... SELECT * FROM 语句。
使用 mysqldump 并重新加载该文件。
回答by DeeCee
The simple way
简单的方法
Change to the database directory:
切换到数据库目录：
cd /var/lib/mysql/
Shut down MySQL... This is important!
关闭 MySQL... 这很重要！
/etc/init.d/mysql stop
Okay, this way doesn't work for InnoDB or BDB-Databases.
好的，这种方式不适用于 InnoDB 或 BDB 数据库。
Rename database:
重命名数据库：
mv old-name new-name
...or the table...
……或者桌子……
cd database/
mv old-name.frm new-name.frm
mv old-name.MYD new-name.MYD
mv old-name.MYI new-name.MYI
Restart MySQL
重启 MySQL
/etc/init.d/mysql start
Done...
完毕...
OK, this way doesn't work with InnoDB or BDB databases. In this case you have to dump the database and re-import it.
好的，这种方式不适用于 InnoDB 或 BDB 数据库。在这种情况下，您必须转储数据库并重新导入它。
回答by Grijesh Chauhan
You may use this shell script:
你可以使用这个 shell 脚本：
Reference: How to rename a MySQL database?
#!/bin/bash
set -e # terminate execution on command failure
mysqlconn="mysql -u root -proot"
olddb=
newdb=
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
WHERE table_schema='$olddb'")
for name in $params; do
$mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name";
done;
$mysqlconn -e "DROP DATABASE $olddb"
It's working:
它正在工作：
$ sh rename_database.sh oldname newname
回答by Amr Mostafa
I've only recently came across a very nice way to do it, works with MyISAM and InnoDB and is very fast:
我最近才遇到一个非常好的方法，它可以与 MyISAM 和 InnoDB 一起使用并且速度非常快：
RENAME TABLE old_db.table TO new_db.table;
I don't remember where I read it but credit goes to someone else not me.
我不记得我在哪里读过它，但功劳归于其他人而不是我。
回答by Steve Chambers
Simplest bullet-and-fool-proof way of doing a completerename (including dropping the old database at the end so it's a rename rather than a copy):
进行完整重命名的最简单的防弹和万无一失的方法（包括在最后删除旧数据库，因此它是重命名而不是副本）：
mysqladmin -uroot -pmypassword create newdbname
mysqldump -uroot -pmypassword --routines olddbname | mysql -uroot -pmypassword newdbname
mysqladmin -uroot -pmypassword drop olddbname
Steps:
脚步：
- Copy the lines into Notepad.
- Replace all references to "olddbname", "newdbname", "mypassword" (+ optionally "root") with your equivalents.
- Execute one by one on the command line (entering "y" when prompted).
- 将这些行复制到记事本中。
- 将所有对“olddbname”、“newdbname”、“mypassword”（+ 可选的“root”）的引用替换为您的等效项。
- 在命令行上一一执行（提示时输入“y”）。
