MySQL table management

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