How to find and change the engines of MySQL tables

Mostly programmers use two types of engines for MySQL DB. They are Innodb, MyISAM. You can check this details very easily in PHPMyAdmin. But if you are in MySQL interactive mode in command prompt. you cant get the details that easily from your DB. But the details of your DB, Tables etc .. are maintained in a db called information_schema. So in order to fetch the details of the engines being used type the following query:

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name];

 

This will list all your tables with their respective engines being used. Incase you know table_name, you can add that as a condition to your query

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name];

 

 

To change the Engines

 

Alter table table_name Engine=[engine you want to change]

Some Interesting statement terminators

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\g

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\G

 

Instead of semicolon, use \g and \G and see the difference :)