How to view the storage engine type of tables in mysql

Mysql supports multiple storage engines. When processing different types of applications, you can use different storage engines to improve application efficiency or provide flexible storage.

Mysql storage engines include MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, and FEDERATED. InnoDB and BDB provide transaction security tables, other storage engines are non-transaction security tables.

There are several methods to view the table storage engine type in mysql:

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

Mysql> show table status from mytest where Name = 'test'; + ------------ + -------- + --------- + ------------ + ------ + ------------------ + ------------- + (partial results are omitted) | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | (Omitted partial results) + ------------ + -------- + --------- + ------------ + ------ + ---------------- + ------------- + (Omitted partial results) | test | MyISAM | 10 | Fixed | 0 | 0 | (Omitted partial results) + ------------ + -------- + --------- + ------------ + ------ + -------------- + --------------- + (Omitted partial results) 1 row in set (0.02 sec) mysql>

The value of the Engine is MyISAM.

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

Mysqlshow-uroot-p 'mypassword' -- status mytest testDatabase: mytest Wildcard: test + ------------ + -------- + --------- + ------------ + ------ + -------------- + ------------- + (partial results omitted) | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | (Omitted partial results) + ------------ + -------- + --------- + ------------ + ------ + ---------------- + ------------- + (Omitted partial results) | test | MyISAM | 10 | Fixed | 0 | 0 | (Omitted partial results) + ------------ + -------- + --------- + ------------ + ------ + -------------- + --------------- + (Omitted partial results)

The value of the Engine is MyISAM.

3. show create table name

This method is sometimes inaccurate, for example:

The InnoDB Storage engine is not enabled in the server configuration. The InnoDB Storage engine is set during table creation. The command used to create a table is as follows:

Mysql> create database mytest;

Query OK, 1 row affected (0.02 sec) mysql> use mytest; Database changedmysql> 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>

In this case, use the methods 1 and 2 above to check that the engine used by the test table is MyISAM, but use 3 to view 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 returned result is "Support" corresponding to "InnoDB" equal to "NO", indicating that the InnoDB Storage engine is not enabled.

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

This article is from the "Bing Lincheng" blog, please be sure to keep this source http://308107405.blog.51cto.com/6396647/1349447