How do I know if a mysql table is using myISAM or InnoDB Engine?

MysqlInnodbMyisamStorage Engines

Mysql Problem Overview

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables. However, you can specify a storage engine to be used during one session with:

SET storage_engine=InnoDB;

So you don't have to specify it for each table.

How do I confirm, if indeed all the tables are using InnoDB?

Mysql Solutions

Solution 1 - Mysql

If you use SHOW CREATE TABLE, you have to parse the engine out of the query.

Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).

The correct query to use is SHOW TABLE STATUS - you can get information on all the tables in a database:

SHOW TABLE STATUS FROM `database`;

Or for a specific table:

SHOW TABLE STATUS FROM `database` LIKE 'tablename';

One of the columns you will get back is Engine.

Solution 2 - Mysql

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db name' AND ENGINE != 'InnoDB'

Solution 3 - Mysql

show create table <table> should do the trick.

Categories
Recommended Mysql Solutions
Recommended Innodb Solutions
Recommended Myisam Solutions
Recommended Storage Engines Solutions

Previous Solution:

iOS: How to get a proper Month name from a number?

IosNumbersNsdateformatter

Next Solution:

Should I be using assert in my PHP code?

PhpAssert

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionkamalView Question on Stackoverflow
Solution 1 - MysqlTehShrikeView Answer on Stackoverflow
Solution 2 - MysqlThe Scrum MeisterView Answer on Stackoverflow
Solution 3 - MysqlMadison CaldwellView Answer on Stackoverflow