说明：
当我们创建表 “test”表时CREATE TABLE test (id INT(11) default NULL auto_increment, s char(60) default NULL, PRIMARY KEY(id) 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 testDatabase:mytest Wildcard: test +------------+--------+---------+------------+------+----------------+-------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | +------------+--------+---------+------------+------+----------------+-------------+ | test | MyISAM | 10 | Fixed | 0 | 0 | 0 | +------------+--------+---------+------------+------+----------------+-------------+
最后：可以看出，在未启用InnoDB存储引擎的情况下，我们发现4，5步返回的结果是正确的，列Engine为MyISAM而不是InnoDB存储引擎。而第3步使用 “SHOW CREATE TABLE 表名” 来查看表使用的mysql存储引擎是不准确的。
转载请注明： »
http://coolnull.com/2759.html