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, sometimes you may need to convert an existing table storage engine into another storage engine, there are many ways to complete this conversion, each method will have advantages and disadvantages, should be based on the actual situation to choose, the following three ways to convert the MySQL table engine:
ALTER TABLEThe simplest way to convert the MySQL table engine is to modify the table definition directly using ALTER TABLE. The following statement converts the MyTable table engine to InnoDBmysql>alter TABLE mytable ENGINE = InnoDB;Pros: Simple, can be used in any of the table engine. Cons: It takes a long time, the process of converting is to define a new engine table and then copy the data from the original table. During replication, the system consumes a large amount of I/O capability while adding read locks to the original table. So it doesn't work to use this kind of transformation in a very busy system, even though it's simple. Also, it is important to note that if you convert the table engine in this way, it loses the attributes associated with the original table engine. For example, converting a innodb table into a MyISAM table and then converting it into a innodb table, even though you did nothing, the foreign keys in the original table would disappear.
Import and Exportto better control the conversion process, you can use the Mysqldump tool to export the data to a file, then modify the engine option of the CREATE TABLE statement in the file, and note that the table name is modified because it is not possible to have two tables with the same name in a database. They use different storage engines on the fly.
shell>
mysqldump-u user name-P database Name Table name > file name
vi Modifying the CREATE TABLE statement in a file
mysql>
source with path file name
Create and QueryThe third approach combines the first efficient and second approach to security. Instead of exporting the entire table's data, create a new storage engine table first, and then take advantage of the 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
