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

3楼-- · 2020-06-30 12:13


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


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

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.

登录 后发表回答