如何检查特定表的 MySQL 引擎类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/213543/
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
How can I check MySQL engine type for a specific table?
提问by oneself
My MySQL database contains several tables using different storage engines (specifically myisam and innodb). How can I find out which tables are using which engine?
我的 MySQL 数据库包含几个使用不同存储引擎（特别是 myisam 和 innodb）的表。如何找出哪些表正在使用哪个引擎？
回答by Greg
SHOW TABLE STATUS WHERE Name = 'xxx'
SHOW TABLE STATUS WHERE Name = 'xxx'
This will give you (among other things) an Enginecolumn, which is what you want.
这将为您提供（除其他外）一Engine列，这正是您想要的。
回答by Jocker
To show a list of all the tables in a database and their engines, use this SQL query:
要显示数据库中所有表及其引擎的列表，请使用以下 SQL 查询：
SELECT TABLE_NAME,
ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname';
Replace dbnamewith your database name.
替换dbname为您的数据库名称。
回答by Javier
SHOW CREATE TABLE <tablename>;
Less parseable but more readable than SHOW TABLE STATUS.
比SHOW TABLE STATUS.
回答by Javier
or just
要不就
show table status;
show table status;
just that this will llist all tables on your database.
只是这将列出数据库中的所有表。
回答by Evan Donovan
Bit of a tweak to Jocker's response (I would post as a comment, but I don't have enough karma yet):
对 Jocker 的回应稍作调整（我会发表评论，但我还没有足够的业力）：
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' AND ENGINE IS NOT NULL;
This excludes MySQL views from the list, which don't have an engine.
这从列表中排除了没有引擎的 MySQL 视图。
回答by Nicholas
SHOW CREATE TABLE <tablename>\G
will format it much nicer compared to the output of
与输出相比，它的格式会更好
SHOW CREATE TABLE <tablename>;
The \Gtrick is also useful to remember for many other queries/commands.
该\G技巧对于记住许多其他查询/命令也很有用。
回答by magic
mysqlshow -i <database_name>
will show the info for all tables of a specific database.
将显示特定数据库的所有表的信息。
mysqlshow -i <database_name> <table_name>
will do so just for a specific table.
将仅针对特定表执行此操作。
回答by zahid
show table status from database_name;
从 database_name 显示表状态；
It will list all tables from the mentioned database.
Example output
它将列出上述数据库中的所有表。
示例输出
show table status where name=your_desired_table_name;
显示表状态，其中 name=your_desired_table_name;
It will show the storage engine used by the mentioned table.
它将显示上述表使用的存储引擎。
回答by T30
If you're using the MySQL Workbench, you can right-click the table and select 'alter table'.
如果您使用的是 MySQL Workbench，则可以右键单击该表并选择“alter table”。
In that window you can see your table Engine and also change it.
在该窗口中，您可以看到您的表引擎并对其进行更改。
回答by David Thomas
Yet another way, perhaps the shortest to get status of a single or matched set of tables:
另一种方式，也许是获取单个或匹配的一组表的状态的最短方法：
SHOW TABLE STATUS LIKE 'table';
You can then use LIKE operators for example:
然后您可以使用 LIKE 运算符，例如：
SHOW TABLE STATUS LIKE 'field_data_%';
