How to change Table Engine in MySQL?

You can change table engine with the help of alter command. The syntax is as follows −

alter table yourTableName ENGINE = yourEngineName;

To understand the above syntax let us create a table with engine MyISAM. Later you can change any other engine. The following is the query to create a table.

mysql> create table ChangeEngineTableDemo
−> (
   −> MovieId int,
   −> MovieName varchar(100),
   −> IsPopular bool
−> )ENGINE = 'MyISAM';
Query OK, 0 rows affected (0.37 sec)

Look at the above query, the table engine is MyISAM, now you can change it to any other engine. Here, we will change engine type InnoDB. The query to change engine type is as follows −

mysql> alter table ChangeEngineTableDemo ENGINE = InnoDB;
Query OK, 0 rows affected (2.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

To check the engine type has been changed or not with the help of show command, the following is the query −

mysql> show create table ChangeEngineTableDemo;

The following is the output −

+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                                                                                        |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ChangeEngineTableDemo | CREATE TABLE `changeenginetabledemo` (
`MovieId` int(11) DEFAULT NULL,
`MovieName` varchar(100) DEFAULT NULL,
`IsPopular` tinyint(1) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci                                                                                                            |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Vrundesha Joshi

Updated on: 29-Jun-2020

248 Views

Kickstart Your Career

Get certified by completing the course

Get Started