Using rename table in MySQL to quickly and efficiently RENAME a database (schema)

Using rename table in MySQL to quickly and efficiently RENAME a database (schema)

Once a new database (schema) is created in MySQL, it cannot be renamed, therefore, if you need to rename a database, the general practice is to dump the data in the target database and import it to the new database you want to rename. A large number of insert statements are used in MySQL import, DML is widely used. If it is a database with a large amount of data, it is very inefficient. In fact, in MySQL, you can use rename table as the DDL method to move TABLE data and then RENAME the database, the following is a brief introduction. First, rename table is easy to use:

RENAME  TABLE  old_table  TO  new_table;

In this way, you can query the information_schema.TABLES dictionary table of MySQL to obtain the data of the corresponding target database. For example, you need to rename a database named test in MySQL to test2:

CREATE  DATABASE  Test2; # create test2 first  SELECT     CONCAT( 'RENAME TABLE test.' ,TABLE_NAME, ' TO test2.' ,TABLE_NAME, ';' ) DDL FROM     information_schema.`TABLES` WHERE     TABLE_SCHEMA =  'test' ; # Generate the DDL for test to be transferred to test2 and then copy and execute DROP  DATABASE  Test; # Delete the old database

However, you must note that this method requires certain permissions.