MySQL - MyISAM or Innodb - How to know the MySQL engine of a table?
To list all tables and the corresponding MySQL Engine types from a schema:
SHOW TABLE STATUS;
The statement above will the full info of all tables, including data_length, index_length, etc.
If you want to see the table name and engine type only, use this alternative statement:
SELECT
TABLE_NAME,
ENGINE
FROM
information_schema.tables
WHERE
TABLE_SCHEMA = 'world';
To show the MySQL Engine of a specific table:
SHOW TABLE STATUS
WHERE
NAME = 'temp_class';
You can also see the Engine Type from the table definition:
SHOW CREATE TABLE table_name;
In TablePlus, you can quickly see the table definition by switching to the structure tab at the bottom of the windows, then click on the definition button at the top. The data type will be displayed within the table creation code.
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.
