在 MySQL 中按引擎显示表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1718126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show tables by engine in MySQL
提问by Kit Peters
How would I show all tables in MySQL with a given engine, e.g. InnoDB, MyISAM, FEDERATED?
我将如何使用给定的引擎（例如 InnoDB、MyISAM、FEDERATED）显示 MySQL 中的所有表？
回答by ChssPly76
Use INFORMATION_SCHEMA.TABLEStable:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'InnoDB'
回答by Alvin
If you want the results from a single database
如果您想要来自单个数据库的结果
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'InnoDB';
回答by MTK
Other examples here.
这里的其他例子。
All tables by engine (except system tables):
所有引擎表（系统表除外）：
SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
All tables except engine (except system tables):
除引擎外的所有表（系统表除外）：
SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE != 'MyISAM' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
回答by Stevan Tosic
If some has problem and want to see in which DB is tables with specific engine
如果有些人有问题并想查看哪些数据库是具有特定引擎的表
SELECT
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database1'
AND engine = 'MyIsam'
) as database1,
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database2'
AND engine = 'MyIsam'
) as database2,
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database3'
AND engine = 'MyIsam'
) as database3;
Regards.
问候。
