Fast and efficient renaming of libraries using rename table in MySQL (schema)

The current MySQL once created a new library (schema) can not be renamed it, so if you need to rename a library to the general practice is to dump the data in the target library to import the new library to rename, in the MySQL import is using a large number of inserts, A large number of DML use, if it is a large amount of data library that is very inefficient, in fact, in MySQL can use rename table This DDL way to move table data later renamed the library, the following is a brief introduction. First, the RENAME table is simple to use:

RENAME TABLE old_table to new_table;

This can be done by querying the MySQL information_schema in peacetime. Tables This dictionary table obtains data for the corresponding target library, such as a library with a test in MySQL that needs to be renamed to Test2:

CREATE DATABASE test2; #先建好test2SELECT CONCAT (' RENAME TABLE test. ', table_name, ' to test2. ', table_name, '; ') Ddlfrom information_schema. ' TABLES ' WHERE table_schema = ' test '; #生成test转移至test2的DDL后复制执行DROP DATABASE test; #最后再删除旧库

However, it is important to note that this approach requires a certain amount of authority

Fast and efficient renaming of libraries using rename table in MySQL (schema)