Summary: in this tutorial, you will learn how to which storage engine that a table is using and how to change the storage engine of the table to a different one.
MySQL supports many kinds of storage engines that provide different capabilities and characteristics. For example, the InnoDB tables support transaction, whereas MyISAM does not.
Querying the current storage engine of a table
There are several ways to get the current storage engine of a table.
The first way to check the current storage engine of a table is to query data from the tables
table in the information_schema
database.
For example, to get the current storage engine of the offices
table in the classicmodels sample database, you use the following query:
SELECT
engine
FROM
information_schema.tables
WHERE
table_schema = 'classicmodels'
AND table_name = 'offices';
Code language: SQL (Structured Query Language) (sql)
![MySQL Change Storage Engine Example](https://www.mysqltutorial.org/wp-content/uploads/2016/01/MySQL-Change-Storage-Engine-Example.jpg)
The second way to query the storage engine of a table is to use the SHOW TABLE STATUS
statement as follows:
SHOW TABLE STATUS LIKE 'offices';
Code language: SQL (Structured Query Language) (sql)
![MySQL Show Table Status Example](https://www.mysqltutorial.org/wp-content/uploads/2016/01/MySQL-Show-Table-Status-Example.jpg)
The third way to get the storage engine of a table is to use the SHOW CREATE TABLE
statement.
SHOW CREATE TABLE offices;
Code language: SQL (Structured Query Language) (sql)
mysql> SHOW CREATE TABLE offices\G;
*************************** 1. row ***************************
Table: offices
Create Table: CREATE TABLE `offices` (
`officeCode` varchar(10) NOT NULL,
`city` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`postalCode` varchar(15) NOT NULL,
`territory` varchar(10) NOT NULL,
PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
Code language: SQL (Structured Query Language) (sql)
MySQL showed the offices
table uses the InnoDB storage engine.
MySQL changing storage engine
Once you have the information of the storage engine of a table, you can change it using the ALTER TABLE statement as follows:
ALTER TABLE table_name ENGINE engine_name;
Code language: SQL (Structured Query Language) (sql)
To check which storage engine that your MySQL server currently supports, you use the SHOW ENGINES statement as follows:
SHOW ENGINES;
Code language: SQL (Structured Query Language) (sql)
![MySQL SHOW ENGINE](https://www.mysqltutorial.org/wp-content/uploads/2016/01/MySQL-SHOW-ENGINE.jpg)
For example, to change the storage engine of the offices
table from InnoDB to MyISAM, you use the following statement:
ALTER TABLE offices ENGINE = 'MYISAM';
Code language: SQL (Structured Query Language) (sql)
In this tutorial, we have shown you how to query the current storage engine of a table and how to change it to a different storage engine using the ALTER TABLE
statement.