How to check the storage engine type of a table in mysql

How to check the storage engine type of the table in mysql: 1. Use [show table status from database name where name='table name']; 2. Check whether the InnoDB storage engine is enabled on the mysql server.

How to view the storage engine type of the table in mysql:

1.show table status from database name where name='table name', example:

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>

See that the value corresponding to Engine is MyISAM

2.mysqlshow -u database login account username -p'database login account password' - -status database library name table name

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 |(省略部分结果)
+------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

You can see that the value corresponding to Engine is MyISAM

3.show create table table name

This method is sometimes inaccurate , Example:

The server configuration does not enable the InnoDB storage engine. When creating the table, the InnoDB storage engine is set. The command when creating the table:

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>

At this time, use the above method 1 and If you use 2 to view, you will see that the engine used by the test table is MyISAM, but if you use 3 to view, you will see the above results. In fact, the storage engine used by the test table is MyISAM.

4. Check whether the InnoDB storage engine is enabled on the mysql server:

The return result is: "InnoDB" corresponding "Support" is equal to "NO", indicating that the InnoDB storage engine is not enabled.

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)
mysql>

Related learning recommendations: mysql tutorial

The above is the detailed content of How to check the storage engine type of a table in mysql. For more information, please follow other related articles on the PHP Chinese website!