查看mysql当前表使用的存储引擎

81 阅读 0 评论 54 点赞

我是靠谱客的博主激昂灰狼，最近开发中收集的这篇文章主要介绍查看mysql当前表使用的存储引擎，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

说明：
当我们创建表 “test”表时

1.CREATETABLEtest (
2. id INT(11) defaultNULLauto_increment,
3. s char(60) defaultNULL,
4. PRIMARYKEY(id)
5.) ENGINE=InnoDB;

一般情况这样没任何问题。但是，如果MySQL服务器配置中未启用InnoDB存储引擎。则在创建表 test 时，MySQL还是会自动选择默认的存储引擎MyISAM来创建test表。因为通过SHOW CREATE TABLE 表名来查看表使用的mysql存储引擎是不准确的。

实例：
mysql服务器未启用InnoDB存储引擎；
库名：mytest；
表名：test(mytest.test)；
帐号：root；
密码：mypassword；

列 “Engine” 下显示的值表示表正在使用的 MySQL 存储引擎。
1.确认 MySQL 服务器是否启用InnoDB存储引擎

mysql> SHOW  ENGINES;
+------------+---------+----------------------------------------------------------+
| Engine     | Support | Comment                                                  |
+------------+---------+----------------------------------------------------------+
| InnoDB     | NO      | Supports transactions, row-level locking, and foreign keys|
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                  |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disa
| CSV        | YES     | CSV storage engine                                       |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables|
| FEDERATED  | NO      | Federated MySQL storage engine                           |
| ARCHIVE    | YES     | Archive storage engine                                   |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance|
+------------+---------+----------------------------------------------------------+
8 rows in set (0.00 sec)

返回结果是：InnoDB对应的Support为NO，表示未启用InnoDB存储引擎。

2.创建表 “test”

mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)
mysql> use mytest;
Database changed
mysql> CREATE TABLE test (
 -> id INT(11) default NULL auto_increment,
 -> s char(60) default NULL,
 -> PRIMARY KEY (id)
 -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql>

3.使用“SHOW CREATE TABLE 表名” 来查看，这种方式是不准确的

mysql> SHOW CREATE TABLE test;
+-------+----------------------------------------------------------------------------+
| Table | Create Table|
+-------+----------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s` char(60) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到test表还是InnoDB引擎！！

4.使用SHOW TABLE STATUS from 数据库库名 where Name=’表名’;这是正确的方式

# mysql -uroot -p'mypassword'
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 221
Server version: 5.1.41-3ubuntu12.7 (Ubuntu)
  
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> SHOW TABLE STATUS from mytest where Name='test';
+------------+--------+---------+------------+------+----------------+-------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+------------+--------+---------+------------+------+----------------+-------------+
| test | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
+------------+--------+---------+------------+------+----------------+-------------+
1 row in set (0.02 sec)
mysql>

5.mysqlshow -u 数据库登录帐号 -p ‘数据库登录帐号密码’ –status 数据库库名表名，这也是正确的方式

# mysqlshow  -uroot -p'mypassword'   --status mytest test
Database:mytest  Wildcard: test
+------------+--------+---------+------------+------+----------------+-------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+------------+--------+---------+------------+------+----------------+-------------+
| test | MyISAM |      10 | Fixed      |    0 |              0 |           0 |
+------------+--------+---------+------------+------+----------------+-------------+