How to change the storage engine of mysql to innodb

Two modification methods: 1. Use the SET statement to temporarily modify the default storage engine. The syntax is "SET default_storage_engine=innodb;". When the client is restarted, it will be restored to the original engine type. 2. Use the ALTER TABLE statement to modify the syntax "ALTER TABLE table name ENGINE=innodb;" to modify the engine type of the specified table to innodb.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Two methods for mysql to modify the storage engine to innodb

1. Use the SET statement to temporarily modify

Use the following statement to modify the temporary default storage engine of the database:

SET default_storage_engine=< 存储引擎名 >

Example: Modify the default storage engine to innodb

SET default_storage_engine=innodb;

At this point, you can find that MySQL's default storage engine has become InnoDB. But when the client is restarted, it will revert to the original engine type.

Note: Prior to MySQL 5.5, MyISAM was the default storage engine when you created a table without explicitly specifying a storage engine. Starting with version 5.5, MySQL uses InnoDB as the default storage engine.

2. Use the ALTER TABLE statement to modify

The syntax format of the storage engine for modifying the data table in MySQL is as follows:

ALTER TABLE <表名> ENGINE=<存储引擎名>;

• ENGINE keyword is used to specify a new storage engine.

Example demonstration

The storage engine of the data table contacts is modified to InnoDB.

Before modifying the storage engine, first use the SHOW CREATE TABLE statement to view the current storage engine of the contacts table

SHOW CREATE TABLE contacts \G

You can see that the current storage engine of the contacts table The engine is MyISAM.

Next, modify the storage engine of the contacts table to the InnoDB type. The SQL statement is:

ALTER TABLE contacts ENGINE=InnoDB;

Use the SHOW CREATE TABLE statement to view the storage engine of the contacts table again, and you will find that The storage engine of the contacts table has become "InnoDB"

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to change the storage engine of mysql to innodb. For more information, please follow other related articles on the PHP Chinese website!