This paper summarizes some methods of viewing and modifying the storage engine under MySQL. The test and validation environment is MySQL 5.6. If there are any differences, please refer to the actual version.

 

1: View MySQL Storage Engine Information

 

1.1 Use the show engine command.

Support column, YES indicates that the current version supports the storage engine, and DEFAULT indicates that the engine is the default engine. NO indicates that the storage engine is not supported. As shown below, InnoDB is the default storage engine.

 

1.2 You can view the system variable default_storage_engine or storage_engine

 

default_storage_engine represents the default storage engine for permanent tables.

2: default_tmp_storage_engine represents the default storage engine for temporary tables.

Storage_engine is not recommended as a system variable. It has been replaced by default_storage_engine.

 

mysql> show variables like '%storage_engine%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| default_storage_engine     | InnoDB |

| default_tmp_storage_engine | InnoDB |

| storage_engine             | MyISAM |

+----------------------------+--------+

3 rows in set (0.01 sec)

mysql> 

 

2: How to modify MySQL's default storage engine?

 

2.1 Modify my.cnf, add the parameter default-storage-engine in the configuration file, and restart the database service.

[mysqld]

default-storage-engine=MyISAM

 

Then check the default storage engine and you will see that MyISAM is the default storage engine.

 

 

2.2 Use commands to modify system variables

 

 

Note that the system variable default_storage_engine is BOTH (global and temporary) and can be dynamically modified. Note, however, that even if you modify the default_storage_engine system variable, if default-storage-engine values are set in your my.cnf configuration file, then my.cnf values will override the system variable values you set. That is to say, the parameter priority in my. CNF will be higher.

 

3: How to view the storage engine used by tables?

 

The storage engine used to view tables has the following methods:

 

3.1 Query information_schema.TABLES

mysql> SELECT TABLE_SCHEMA, 

    ->        TABLE_NAME, 

    ->        TABLE_TYPE, 

    ->        ENGINE 

    -> FROM   information_schema.TABLES 

    -> WHERE  TABLE_NAME = 'TEST'; 

+--------------+------------+------------+--------+

| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |

+--------------+------------+------------+--------+

| gsp          | TEST       | BASE TABLE | InnoDB |

+--------------+------------+------------+--------+

1 row in set (0.00 sec)

mysql> 

 

3.2 Use the SHOW CREATE TABLE TESTG command.

 

mysql> SHOW CREATE TABLE TEST \G;

*************************** 1. row ***************************

       Table: TEST

Create Table: CREATE TABLE `TEST` (

  `ID` int(11) DEFAULT NULL,

  `COL1` varchar(6) DEFAULT NULL,

  `COL2` varchar(6) DEFAULT NULL,

  `COL3` varchar(6) DEFAULT NULL,

  `COL4` varchar(6) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> 

 

3.3 SHOW TABLE STATUS

mysql> SHOW TABLE STATUS WHERE Name='TEST';

ERROR 1046 (3D000): No database selected

mysql> 

mysql> 

mysql> use gsp;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLE STATUS WHERE Name='TEST';

 

As shown above, this writing must be database-based, otherwise, you can only use the following grammar

SHOW TABLE STATUS [{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

mysql> SHOW TABLE STATUS FROM gsp WHERE Name='TEST';

 

4: How to modify the storage engine of related tables?

 

The storage engine for modifying tables is very simple, and the syntax is as follows

ALTER TABLE my_table ENGINE = InnoDB;

mysql> ALTER TABLE TEST ENGINE=MyISAM;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE TEST \G;

*************************** 1. row ***************************

       Table: TEST

Create Table: CREATE TABLE `TEST` (

  `ID` int(11) DEFAULT NULL,

  `COL1` varchar(6) DEFAULT NULL,

  `COL2` varchar(6) DEFAULT NULL,

  `COL3` varchar(6) DEFAULT NULL,

  `COL4` varchar(6) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> 

 

5: How to specify the storage engine when creating tables.

 

When creating a table, if you want to specify the storage engine, you only need to set the parameter ENGINE. It's simple.

 

mysql> CREATE TABLE TEST1 (ID INT) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

mysql>  CREATE TABLE TEST2 (ID INT) ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

 

Reference material:

https://dev.mysql.com/doc/refman/5.6/en/storage-engine-setting.html

https://dev.mysql.com/doc/refman/5.7/en/storage-engine-setting.html