如何知道 mysql 表是使用 myISAM 还是 InnoDB 引擎？

在 MySQL 中，无法为特定数据库指定存储引擎，只能为单个表指定存储引擎。但是，您可以指定一个存储引擎在一个会话期间使用:

SET storage_engine=InnoDB;

所以不必为每个表指定它。

如果确实所有的表都在使用 InnoDB，我如何确认？

71767 次浏览
小开 
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db name' AND ENGINE != 'InnoDB'
小开

show create table <table> should do the trick.

小开
最佳答案

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.


提交答案