Three methods to modify database engine _ MySQL

Three methods to modify the database engine bitsCN.com

Three methods to modify the database engine

1. directly alter table

SQL code

Alter table youTable ENGINE = InnoDB;

This method is the easiest, but it takes a long time for a big data table, because MySQL needs to perform row-by-row replication from the old table to the new table. MySQL locks the entire table regardless of the engine used to operate the alter table operation.

2. use dump and source

First, dump the required TABLE, then modify the dump file, remove drop table, modify the create table code, and execute source.

In this way, the engine cannot be modified online, and the database must be deprecated or synchronized after being modified online.

3. use CREATE and SELECT

SQL code

Create table myTableCopy LIKE myTable;

Alter table myTableCopy ENGINE = InnoDB;

Insert into myTableCopy SELECT * FROM myTable WHERE id BETWEEN x AND y;

In this way, when the table data volume is large, you can import data in batches according to the range without locking myTable.

BitsCN.com