MySQL introduces three ways to modify the data table storage engine

As the most commonly used database, MySQL often encounters a variety of problems. Today we're going to talk about changes to the table storage engine. There are three ways, listed below.

1. True modification. It is slow when there is a lot of data and can affect read performance when modified. my_table is the table of operations, and innoDB is the new storage engine.

ALTER TABLE my_table ENGINE=InnoDB

2. Export and import. This is easy to manipulate, just change the exported sql file, and then guide it back. Use mysqldump, Maple often use navicate that is easier to get started. Friendly reminders are risky.

3. Create, insert This one is faster and safer than the first one. It is recommended. Operate in 2 steps

a. Create a table, first create a table similar to the one you want to operate on, then change the storage engine to the target engine.

CREATE TABLE my_tmp_table LIKE my_table;

ALTER TABLE my_tmp_table ENGINE=InnoDB;

b. Insert. For security and speed, it is best to add transactions and limit the id(primary key) scope.

INSERT INTO my_tmp_table SELECT * FROM my_table;

Here, I hope to help the students in need.