MySQL changes the table's storage engine

MySQL provides a variety of database storage engines, and the storage engine is responsible for storing and extracting data from the MySQL database. Different storage engines have different characteristics, and sometimes it may be necessary to convert the storage engine of an existing table into another storage engine. There are many ways to complete such a conversion, each method will have advantages and disadvantages, should be based on the actual situation to choose. Here are three ways to convert the MySQL table engine:
ALTER TABLEThe simplest way to convert a MySQL table engine is to change the table definition directly using ALTER TABLE. The following statement converts the MyTable table engine to InnoDBmysql>alter TABLE mytable ENGINE = InnoDB;Advantages: Simple, can be used for whatever the table engine. Cons: It takes a long time to run, the process of converting is to define a new engine table and then copy the data from the original table.

The

consumes a large amount of system I/O capability during replication. Read locks are also added to the original table at the same time. So it doesn't work to use this kind of transformation in a very busy system, even though it's very easy. In addition, it should be noted that the conversion of the table engine in such a way would lose the attributes associated with the original table engine.

For example, convert a InnoDB table into a MyISAM table and then convert it to a InnoDB table. Even though you haven't done anything, the foreign keys in the original table will disappear.
import and export shell> mysqldump  -u   username  -P   Database name   Table name > file name VI change the CREATE TABLE statement in file mysql>  source with path file name

Span style= "font-size:14px" > Create and query

You do not need to export the entire table of data. Instead, create a new storage engine table first. Then use Insert .... The select syntax comes to the data. mysql>CREATE TABLE innodb_table like myisam_table; mysql>ALTER TABLE innodb_table ENGINE = InnoDB; mysql>INSERT into innodb_table SELECT * from myisam_table;

MySQL changes the table's storage engine