How do I change the database name using MySQL?

2020-06-30 11:39发布

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.

database1 -> database2?

标签: mysql
12条回答
时光不老,我们不散
2楼-- · 2020-06-30 12:13

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 ER diagram for the database. Rename the schema there.

After that, go to the File menu and go to export and forward engineer the database.

Then you can import the database.

查看更多
Melony?
3楼-- · 2020-06-30 12:13

Try:

RENAME database1 TO database2;
查看更多
兄弟一词,经得起流年.
4楼-- · 2020-06-30 12:17

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.

mysql> DROP DATABASE database1;

Note : shell> denote command prompt and mysql> denote mysql prompt.

查看更多
祖国的老花朵
5楼-- · 2020-06-30 12:17

Unfortunately, MySQL does not explicitly support that (except for dumping and reloading database again).

From 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.

查看更多
虎瘦雄心在
6楼-- · 2020-06-30 12:19

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

To use it simply call the renameDatabase procedure.

CALL renameDatabase('old_name', 'new_name');

Tested on MariaDB and should work ideally on all RDBMS using InnoDB transactional engine.

查看更多
劫难
7楼-- · 2020-06-30 12:22

You can change the database name using MySQL interface.

Go to 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.

查看更多
登录 后发表回答