How to change the database name in Mysql Workbench 8.0 using ALTER DATABASE?

Question

I have a database called teste with about 3 tables and me wanted to rename the database of teste for novoteste. I guess that’s what you do with ALTER DATABASE only I don’t know this command.

Answer 1

There is no single command in Mysql to rename a database. You can use the command:

RENAME TABLE banco_atual.nome_tabela TO novo_banco.nome_tabela;

for each of the current bank tables which, for practical purposes, will be as if it had renamed the bank, except for the fact that the current bank still exists, but without any table. Later you can run a DROP DATABASE.

Answer 2

In the Postgresql is as follows:

To change the name of the database:

ALTER DATABASE <nome_do_banco> RENAME TO <novo_nome_do_banco>

To change the name of schema of the database:

ALTER SCHEMA <nome_do_schema> RENAME TO <novo_nome_do_schema>

In the Mysql is as follows:

Creating a database:

CREATE DATABASE teste0;

Creating another database:

CREATE DATABASE teste1;

setting the database that will occur the encoding:

USE teste0;

Creating a table in the bank test:

CREATE TABLE dados(
   id_codigo int primary key not null auto_increment,
   nome varchar (50)
);

Checking the table(s) (s) in the bank test

SHOW TABLES;

Using the RENAME TABLE to transfer the bank table test for teste1:

RENAME TABLE teste0.id_codigo TO teste1.id_codigo,
             teste0.nome TO teste1.nome

setting the database teste1:

USE teste1;

Checking the table(s) (s) in the bank teste1

SHOW TABLES;

setting the database test (to verify):

USE teste0;

Checking the table(s) (s) in the bank test (to check, but there will be nothing)

SHOW TABLES;

If you want to delete the old empty database:

DROP DATABASE teste0;

Answer 3

Using the command to rename a database in mysql often generates a lot of headache...

Instead there are two alternatives:

a) Create a new database with the name you want and then copy all tables from the original database to the new database. After that just delete the original database.

b) Create a dump from the original database and then import it with the new name. Then you delete the original database.

Answer 4

Good afternoon, by command I do not know, but has to change the name of the database by phpMyAdmin going to Login -> Schemas -> Operations -> Rename Database.

Hugs.