How to alter the database engine of a MySQL database table?

First, determine the type of MySQL database i.e. whether its engine is InnoDB or MyISAM. To achieve this, use engine column from the information_schema.columns.tables.

The syntax is as follows.

SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ’yourDatabaseName’
AND TABLE_NAME = ’yourTableName’;

Here, I have a table with the name ‘StudentInformations’ −

mysql> create table StudentInformations
   -> (
   -> StudentId int not null auto_increment,
   -> StudentFirstName varchar(20),
   -> StudentLastName varchar(20),
   -> Primary Key(StudentId)
   -> );
Query OK, 0 rows affected (0.57 sec)

Now you can know the table is using InnoDB or MyISAM using the implementation of above syntax. Our database is ‘test’.

The query is as follows for the same −

mysql> select engine from information_schema.tables
   -> where table_schema = 'test'
   -> and table_name = 'StudentInformations';

The following is the output −

+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.05 sec)

Change the engine of 'StudentInformations' table using alter command. The syntax is as follows to change the engine of any table.

ALTER TABLE yourTableName ENGINE = ‘yourEngineName’;

Let us now change the engine InnoDB to MyISAM. The query is as follows −

mysql> alter table StudentInformations ENGINE = 'MyISAM';
Query OK, 6 rows affected (1.84 sec)
Records − 6 Duplicates − 0 Warnings − 0

The result displayed above shows 6 rows are affected because there are 6 rows in the table.

To check whether the table is converted from InnoDB to MyISAM or not, the following is the query −

mysql> select engine from information_schema.tables
-> where table_schema = 'test'
-> and table_name = 'StudentInformations';

Here is the output displaying the engine have been updated successfully −

+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)

Arjun Thakur

e

Updated on: 30-Jul-2019

320 Views

Kickstart Your Career

Get certified by completing the course

Get Started