如何使用 MySQL 更改数据库名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19806065/
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 change the database name using MySQL?
提问by Jerielle
How can I change the database name of my database?
如何更改我的数据库的数据库名称？
I tried to use the rename database command, but on the documents about it it is said that it is dangerous to use. Then what should I need to do to rename my database name?
我尝试使用重命名数据库命令，但在有关它的文档中说使用它是危险的。那么我需要做什么来重命名我的数据库名称？
For example, if I want to rename my database to this.
例如，如果我想将我的数据库重命名为 this.
database1 -> database2?
采纳答案by Mad Dog Tannen
I don't think it's possible.
我不认为这是可能的。
You can use mysqldumpto dump the data and then create a schema with your new name and then dump the data into that new database.
您可以使用mysqldump转储数据，然后使用您的新名称创建架构，然后将数据转储到该新数据库中。
回答by Nanhe Kumar
Follow bellow steps:
请按照以下步骤操作：
shell> mysqldump -hlocalhost -uroot -p database1 > dump.sql
mysql> CREATE DATABASE database2;
shell> mysql -hlocalhost -uroot -p database2 < dump.sql
If you want to drop database1 otherwise leave it.
如果你想删除 database1 否则离开它。
mysql> DROP DATABASE database1;
Note : shell> denote command prompt and mysql> denote mysql prompt.
注意：shell> 表示命令提示符，mysql> 表示 mysql 提示符。
回答by mvp
Unfortunately, MySQL does not explicitly support that (except for dumping and reloading database again).
不幸的是，MySQL 并没有明确支持（除了转储和重新加载数据库）。
From http://dev.mysql.com/doc/refman/5.1/en/rename-database.html:
从http://dev.mysql.com/doc/refman/5.1/en/rename-database.html：
13.1.32. RENAME DATABASE Syntax
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. ... Use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.
13.1.32。重命名数据库语法
重命名{数据库 | SCHEMA} db_name TO new_db_name;
该语句是在 MySQL 5.1.7 中添加的，但被发现是危险的，并在 MySQL 5.1.23 中删除。... 使用此语句可能会导致数据库内容丢失，这就是它被删除的原因。不要在存在 RENAME DATABASE 的早期版本中使用它。
回答by user1407252
You can change the database name using MySQL interface.
您可以使用 MySQL 界面更改数据库名称。
Go to http://www.hostname.com/phpmyadmin
转到http://www.hostname.com/phpmyadmin
Go to database which you want to rename. Next, go to the operation tab. There you will find the input field to rename the database.
转到要重命名的数据库。接下来，转到操作选项卡。在那里您将找到用于重命名数据库的输入字段。
回答by txyoji
"As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another"
“只要两个数据库在同一个文件系统上，就可以使用 RENAME TABLE 将表从一个数据库移动到另一个数据库”
-- ensure the char set and collate match the existing database.
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
CREATE DATABASE `database2` DEFAULT CHARACTER SET = `utf8` DEFAULT COLLATE = `utf8_general_ci`;
RENAME TABLE `database1`.`table1` TO `database2`.`table1`;
RENAME TABLE `database1`.`table2` TO `database2`.`table2`;
RENAME TABLE `database1`.`table3` TO `database2`.`table3`;
回答by Ashutosh Kumar
You can use below command
您可以使用以下命令
alter database Testing modify name=LearningSQL;
更改数据库测试修改名称=LearningSQL;
Old Database Name = Testing, New Database Name = LearningSQL
旧数据库名称 = 测试，新数据库名称 = LearningSQL
回答by Benny
Another way to rename the database or taking an image of the database is by using the reverse engineering option in the database tab. It will create an ERdiagram for the database. Rename the schema there.
另一种重命名数据库或拍摄数据库图像的方法是使用数据库选项卡中的逆向工程选项。它将为数据库创建一个ER图。在那里重命名架构。
After that, go to the Filemenu and go to export and forward engineer the database.
之后，转到“文件”菜单，然后导出和转发数据库。
Then you can import the database.
然后就可以导入数据库了。
回答by E.R.Rider
After much aggravation this is what I have found to work"simply". First thing, I am using MYSQL Workbench and the import would not work as it should, as the import dump file would always revert to the original schema name. I spent several hours trying every thing to no avail,all for a spelling error. I solved the issue by opening one of the .sql dump files in notebook and hand editing the typo's of the schema name, take care to rename all instances schema name has three in the beginning, save the file and then import. this worked perfectly for me and hope that it will help others looking for the simple answer to changing database names/schema names. One more tip that I have found true, when programs do not do as they should go to the "source" literally find the source code. Hope this helps someone
经过多次恶化，这就是我发现“简单”工作的方法。首先，我使用的是 MYSQL Workbench，导入无法正常工作，因为导入转储文件将始终恢复为原始模式名称。我花了几个小时尝试每件事都无济于事，都是因为拼写错误。我通过在笔记本中打开一个 .sql 转储文件并手动编辑模式名称的拼写错误来解决这个问题，注意重命名所有实例模式名称在开头具有三个，保存文件然后导入。这对我来说非常有效，希望它能帮助其他人寻找更改数据库名称/模式名称的简单答案。我发现的另一个提示是正确的，当程序没有按照他们应该去“源”字面上查找源代码时。希望这有助于某人
Low rep so they wont let me comment on the prior/post answer(it keeps changing rank or position), so I added it here. reverse engineering will work fine as long as there is no data in the sever table. if data exists and you try to update the server after the name change it will either pull an error or just create a new database/schema with no data, I know I tried ten times to no avail. The above works simply and avoids headaches, as one can review the SQL code for other errors if any or change table names or creation data. the .sql file is just a compiled SQL code so in theory one could copy and add it through PHP or the script console of the database management tool.
低代表所以他们不会让我评论之前/之后的答案（它一直在改变排名或位置），所以我在这里添加了它。只要服务器表中没有数据，逆向工程就可以正常工作。如果数据存在并且您尝试在名称更改后更新服务器，它要么会出现错误，要么只是创建一个没有数据的新数据库/模式，我知道我尝试了十次都无济于事。上面的工作很简单，避免了麻烦，因为人们可以检查 SQL 代码是否有其他错误，或者更改表名或创建数据。.sql 文件只是一个编译后的 SQL 代码，因此理论上可以通过 PHP 或数据库管理工具的脚本控制台复制和添加它。
回答by blueberry0xff
I agree with above answers and tips but there is a way to change database name with phpmyadmin
我同意上述答案和提示，但有一种方法可以使用 phpmyadmin 更改数据库名称
Renaming the Database From cPanel, click on phpMyAdmin. (It should open in a new tab.) Click on the database you wish to rename in the left hand column. Click on the Operations tab. Where it says "Rename database to:" enter the new database name. Click the Go button. When it asks you to want to create the new database and drop the old database, click OK to proceed. (This is a good time to make sure you spelled the new name correctly.) Once the operation is complete, click OK when asked if you want to reload the database.
重命名数据库 从 cPanel，单击 phpMyAdmin。（它应该在新选项卡中打开。）在左侧列中单击要重命名的数据库。单击操作选项卡。在显示“将数据库重命名为：”的地方输入新的数据库名称。单击“前往”按钮。当它询问您要创建新数据库并删除旧数据库时，单击确定继续。（这是确保正确拼写新名称的好时机。） 操作完成后，在询问您是否要重新加载数据库时单击“确定”。
here's the video tutorial:
这是视频教程：
回答by shivanshs9
InnoDB supports RENAME TABLE statement to move table from one database to another. To use it programmatically and rename database with large number of tables, I wrote a couple of procedures to get the job done. You can check it out here - SQL script @Gist
InnoDB 支持 RENAME TABLE 语句将表从一个数据库移动到另一个数据库。为了以编程方式使用它并重命名具有大量表的数据库，我编写了几个过程来完成工作。您可以在此处查看 - SQL 脚本 @Gist
To use it simply call the renameDatabaseprocedure.
要使用它，只需调用renameDatabase过程。
CALL renameDatabase('old_name', 'new_name');
Tested on MariaDB and should work ideally on all RDBMS using InnoDB transactional engine.
在 MariaDB 上进行了测试，应该可以在使用 InnoDB 事务引擎的所有 RDBMS 上理想地工作。
