MySQL Change Storage Engine

In this tutorial, we will learn to change the storage engine of MySQL database tables. We will see how can we set the storage engine while creating a table as well as how can we modify the storage engine of the already created table. Here we go!

Also read: PHP-MySQL Transaction – A Complete Guide

Before We Start

Before we see how to change the storage engine, we must know important details about MySQL storage engines.

A storage engine is a software module that performs actions like create, insert, read, and delete in a database management system. Depending on your application and use case, you may choose from a variety of storage engines for MySQL DBMS.

It’s critical to comprehend the advantages of each storage engine so that you may take advantage of them and put them to good use. If you’re learning the MySQL database, knowing everything about the storage engine isn’t necessary. However, if you’re creating a production database, you’ll need to know about each storage engine.

For MySQL 5.5 and subsequent versions, InnoDB is the default storage engine. MyISAM was MySQL’s default storage engine prior to version 5.5. Choosing the best storage engine is a strategic decision that will have a big impact on future development.

List of MySQL Storage Engines

You can see the list of available storage engines using a simple command-

SHOW ENGINES;Code language: SQL (Structured Query Language) (sql)

You can see all the storage engines with their properties such as support, comment, transaction and savepoints.

Set Storage Engine While a Creating Table

As stated earlier, InnoDB is the default storage engine while creating a table. However, we can modify it.

CREATE TABLE SETut(
Id INT PRIMARY KEY, 
Name VARCHAR(50)
) ENGINE='MyISAM';Code language: SQL (Structured Query Language) (sql)

When we execute the above query, the table of type MyISAM will be created.

Get Storage Engine of Table

We can also get the storage engine of a table that we have already created.

We will write a query to display the storage engine of the table SETut that we created earlier.

SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='journaldev'
AND TABLE_NAME='SETut';Code language: SQL (Structured Query Language) (sql)

Here, TABLE_SCHEMA is the name of your database and the TABLE_NAME is the table name that you want to check the storage engine of.

Change Storage Engine of a MySQL Table

There are two ways you can change the storage engine of the table- using MySQL CLI or MySQL Workbench.

Both are pretty easy methods and we will show the demonstration in both ways.

Using MySQL CLI

We can modify the storage engine of the existing table using only one SQL statement.

Syntax to modify the storage engine is-

ALTER TABLE table_name ENGINE engine_name;  Code language: SQL (Structured Query Language) (sql)

Now let’s change the storage engine of the table SETut from MyISAM to InnoDB.

ALTER TABLE SETut ENGINE = 'InnoDB';  Code language: SQL (Structured Query Language) (sql)

As you can see, the storage engine of the SETut table is changed to InnoDB.

Using MySQL WorkBench

If you prefer MySQL workbench over MySQL CLI then this method is for you. It provides you with a graphical user interface to change the storage engine of the table.

Let’s see how can we change the storage engine of the table-

• On the left-hand side, you will see all the databases and tables. Navigate to the table where you want to change the storage engine.
• Right-click on the table and click on the ALTER TABLE option as shown below.

• On the new screen, you will see the table description. Change the Engine to whatever you want as shown below.

• Click on Apply button and on the new screen, you will see the new SQL statement to modify whatever changes you have made. Click on Apply button to save the changes as shown below.

• Now you can check the Engine is changed to the new one.

As you can see, we had changed the storage engine of the SETut table to InnoDB using MySQL CLI. Now, we have changed it back to the MyISAM.

Conclusion

In this tutorial, we have learned to change the storage engine of the table. We have also seen how can we display the storage engine of the existing table and set different storage engines while creating a table. You can read the official documentation of MySQL storage engines for detailed information.

References

MySQL official documentation on storage engines.