How to change mysql database engine for all tables from MyISAM to InnoDB on phpmyadmin?

View Questions › Category: Questions › How to change mysql database engine for all tables from MyISAM to InnoDB on phpmyadmin?
0 Vote Up Vote Down
admin Staff asked 6 years ago

Is there a way to not do this automatically for all tables instead of manually? It takes too long if there are too many tables.

Question Tags: ,
1 Answers
0 Vote Up Vote Down
Best Answer
admin Staff answered 6 years ago

With this you can get phpMyAdmin to list all tables with the sql command.

SELECT concat('alter table`',TABLE_NAME,'` engine=innodb;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '[REPLACE WITH CORRECT DB NAME]' AND ENGINE = 'MyISAM';

Steps:

  1. Go into phpmyadmin and select database
  2. Go to SQL, run command above
  3. Select “Show full text”, and “Show all”
  4. Copy all row and the text
  5. Go to SQL again and paste these commands
  6. Run

All tables are now in InnoDB!