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

Source: Internet
Author: User
Tags mysql import

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.