MySQL table management

Source: Internet
Author: User
1. clone a table

You can use

Create Table... like statement to clone the table structure; Use insert into... select

Statement to clone part or all of the table data.

 

2. Temporary table

Need a table created for temporary use only and automatically deleted? Use a temporary table,

You can use the create temporary table... statement to create a temporary table.

After the connection to the MySQL server is closed, the temporary table is automatically deleted.

 

Common table creation statements:

Create temporary table tbl_name (... column definition ...);

Create a table based on the query results:

Create temporary table tbl_name Select ...;

 

Note that the temporary table name must be unique within the application.

Another feature of a temporary table is that the temporary table can use the table name of a common table.

The result is that during the lifecycle of a temporary table, it will be a normal table with the same name as the temporary table,

With this feature, you can create a temporary backup for a common table and perform

Any operation without affecting real data.

 

3. Check or change the storage engine of a table

MySQL supports multiple storage engines, each of which has different features. For example, InnoDB and bdb support transactions,

MyISAM does not support transactions ). Determine whether a table supports transactions.

The engine used. If you need to change the table in a transaction, but the corresponding engine does not support it, you can

Change the table to an engine that supports transactions.

 

You can use information_schema, show table status, or show create

Table statement to determine the engine currently used by a table. As follows:

(1) select engine from information_schema.tables

Where table_schema = 'mail' and table_name = 'test'

(2) show table status like 'test ';

(3) show create table 'test ';

 

Use alter table and an engine clause to change the engine used by a table. For example:

Alter table mail engine = InnoDB;

Note: it may take a long time to change all the storage engines of a large table.

Cpu and I/o resources.

... To be continued

 

 

 

 

Related Article

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.