How to update MySQL table storage engine

To update MySQL table engine, following the below syntax −

Syntax

alter table yourTableName ENGINE=InnoDB;

Let us first create a table −

mysql> create table DemoTable    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> StudentAge int,    -> StudentCountryName varchar(20)    -> )ENGINE=MyISAM,AUTO_INCREMENT=101; Query OK, 0 rows affected (0.18 sec)

Let us check the description of table −

mysql> show create table DemoTable;

This will produce the following output −

+---------------+-----------------------------------------------------------------------------------------+ | Table         | Create Table                                                                            | +---------------+-----------------------------------------------------------------------------------------+ | DemoTable     | CREATE TABLE `DemoTable` ( `StudentId` int(11) NOT NULL AUTO_INCREMENT, `StudentName`                         varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `StudentAge` int(11) DEFAULT NULL,                   `StudentCountryName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY                            (`StudentId`) ) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8                                        COLLATE=utf8_unicode_ci                                                                | +---------------+------------------------------------------------------------------------------------------+ 1 row in set (0.50 sec)

Here is the query to update MySQL table engine −

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

Let us check the description of table once again −

mysql> show create table DemoTable;

This will produce the following output −

+---------------+-----------------------------------------------------------------------------------------+ | Table         | Create Table                                                                            | +---------------+-----------------------------------------------------------------------------------------+ | DemoTable     | CREATE TABLE `DemoTable` ( `StudentId` int(11) NOT NULL AUTO_INCREMENT, `StudentName`                         varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `StudentAge` int(11) DEFAULT NULL,                   `StudentCountryName` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY                            (`StudentId`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8                                        COLLATE=utf8_unicode_ci                                                                | +---------------+------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)