How to Change Storage Engine For a Table in MySQL

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)

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)

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)

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.

Was this tutorial helpful?