如何找到我的桌子是MyISAM还是Innodb [重复]

Possible Duplicate:
How can I check MySQL engine type for a specific table?

可能重复：如何检查特定表的MySQL引擎类型？

Assuming that users is a table following command does not reveal if users table is MyISAM or Innodb.

假设用户是一个跟随命令的表，则不会显示用户表是MyISAM还是Innodb。

desc users;

How do I find what is the type of users table?

如何找到用户表的类型？

3 个解决方案

#1

You can use SHOW TABLE STATUS to see table information.

您可以使用SHOW TABLE STATUS查看表信息。

SHOW TABLE STATUS WHERE `Name` = 'my_table';

Simply check the value of the Engine column in the returned dataset to know which engine the table is using.

只需检查返回数据集中Engine列的值，即可知道该表正在使用哪个引擎。

#2

SELECT ENGINE
FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_NAME='your_table_name'
AND   TABLE_SCHEMA='your_database_name';
-- or use TABLE_SCHEMA=DATABASE() if you have a default one.

#3

You can use SHOW CREATE TABLE and look for the ENGINE part in the response.

您可以使用SHOW CREATE TABLE并在响应中查找ENGINE部件。

SHOW CREATE TABLE users;

Example:

例：

CREATE TABLE innodb_table (id int, value int) ENGINE=INNODB;
CREATE TABLE myisam_table (id int, value int) ENGINE=MYISAM;
CREATE TABLE default_table (id int, value int);

Result for innodb_table:

innodb_table的结果：

SHOW CREATE TABLE innodb_table;
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| innodb_table | CREATE TABLE `innodb_table` (
  `id` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Result for myisam_table:

myisam_table的结果：

SHOW CREATE TABLE myisam_table;
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| myisam_table | CREATE TABLE `myisam_table` (
  `id` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Result for default_table:

default_table的结果：

SHOW CREATE TABLE default_table;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                      |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
| default_table | CREATE TABLE `default_table` (
  `id` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#1