通过终端查看 MySQL 数据库使用的存储引擎
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10421782/
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
See what storage engine MySQL database uses via terminal
提问by NightHawk
Is there a command in terminal for finding out what storage engine my MySQL database is using?
终端中是否有命令可以找出我的 MySQL 数据库正在使用的存储引擎？
回答by Michael Berkowski
This is available in a few places.
这在几个地方可用。
From the SHOW CREATE TABLEoutput.
从SHOW CREATE TABLE输出。
mysql> SHOW CREATE TABLE guestbook.Guestbook;
+-----------+-------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------+
| Guestbook | CREATE TABLE `Guestbook` (
`NAME` varchar(128) NOT NULL DEFAULT '',
`MESSAGE` text NOT NULL,
`TIMESTAMP` varchar(24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
From information_schema
来自 information_schema
You may also find it in information_schema.TABLESif you want to query the engines of multiple tables.
information_schema.TABLES如果你想查询多个表的引擎，你也可以找到它。
SELECT ENGINE
FROM information_schema.TABLES
WHERE
TABLE_NAME='yourtable'
AND TABLE_SCHEMA='yourdatabase';
回答by Arthur Sigma
SHOW ENGINES;
return the engines your MySQL database support and tell you which is the default one if not otherwise specified at creation time.
返回您的 MySQL 数据库支持的引擎，并告诉您如果在创建时没有另外指定，哪个是默认的。
回答by Marc B
A database on MySQL can use multiple storage engines, so you'll have to check per-table. Simplest is to do
MySQL 上的数据库可以使用多个存储引擎，因此您必须检查每个表。最简单的就是做
show create table yourtable;
and see what the 'engine' line at the end of the DDL statement is. e.g. engine=InnoDB, engine=MyISAM, etc...
并查看 DDL 语句末尾的“引擎”行是什么。例如engine=InnoDB，engine=MyISAM等...
If you want to check all the tables in your DB:
如果要检查数据库中的所有表：
select TABLE_NAME, ENGINE
from information_schema.TABLES
where TABLE_SCHEMA='yourdbname'
回答by Nicola Cossu
This is a longer solution but it can be useful if you want to learn something about information_schema
这是一个较长的解决方案，但如果您想了解有关 information_schema
mysql> select table_name,engine from information_schema.tables where table_name
= 'table_name' and table_schema = 'db_name';
回答by Yago Riveiro
You can use this command:
你可以使用这个命令：
mysql -u[user] -p -D[database] -e "show table status\G"| egrep "(Index|Data)_length" | awk 'BEGIN { rsum = 0 } { rsum += } END { print rsum }'
回答by Kumar Anand
SHOW TABLE STATUS WHERE Name = 'user_tbl'
SHOW TABLE STATUS WHERE Name = 'user_tbl'
回答by Pothi Kalimuthu
mysql -u[user] -p -D[database] -e "show table status\G" | egrep "(Engine|Name)"
This will list all the tables and their corresponding engine. Good to get an overview of everything!
这将列出所有表及其相应的引擎。很高兴了解所有内容！
It's a modified answer from @yago-riveiro where he showed how to get the size of the tables, rather than the engines in use. Also, it's better to have an explanation on what a command does.
这是@yago-riveiro 的修改后的答案，他展示了如何获取表格的大小，而不是使用中的引擎。此外，最好对命令的作用进行解释。
