Shulou(Shulou.com)06/01 Report--

How to view the type of table storage engine in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Check the storage engine type of tables in mysql: 1, use [show table status from database name where name=' table name']; 2, check whether the mysql server enables InnoDB storage engine.

The method to view the storage engine type of the table in mysql:

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

Mysql > SHOW TABLE STATUS from mytest where Name='test' +-+ (omit some results) | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | (omit part) Result) +-+ (omit some results) | test | MyISAM | 10 | Fixed | 0 | 0 | 0 | (omit partial results) +-+ (omitted partial results) 1 row in set (0.02 sec) mysql >

You can see that the corresponding value of Engine is MyISAM

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

Mysqlshow-uroot-pairmypassword`-- status mytest testDatabase:mytest Wildcard: test+-+ (omitting some results) | Name | Engine | Version | Row _ format | Rows | Avg_row_length | Data_length | (partial results are omitted) +-+ (partial results are omitted) | test | MyISAM | 10 | Fixed | 0 | 0 | 0 | (omit some results) +-+ (omit some results)

You can see that the corresponding value of Engine is MyISAM

3.show create table table name

This method is sometimes inaccurate, for example:

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

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 >

At this point, using methods 1 and 2 above, you will see that the engine used by the test table is MyISAM, but using 3 to view will see the result as above. In fact, the storage engine used by test tables is MyISAM.

4. Check to see if the mysql server enables the InnoDB storage engine:

The result returned is that the "Support" corresponding to "InnoDB" equals "NO", indicating that the InnoDB storage engine is not enabled.

Mysql > SHOW ENGINES +-- + (omit some results) | Engine | Support | Comment | | (omit some results) +-- + (omit some results) | InnoDB | NO | Supports transactions | Row-level locking, and foreign keys | (omit 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 (omit partial results) | CSV | YES | CSV storage engine | (omit partial results) | MEMORY | YES | Hash based, stored in memory Useful for temporary tables | (omit partial results) | FEDERATED | NO | Federated MySQL storage engine | (omit partial results) | ARCHIVE | YES | Archive storage engine | (omit partial results) | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | (omit partial results) +- -+-+ (omitting some results) 8 rows in set (0.00 sec) mysql > Thank you for reading! After reading the above, do you have a general idea of how to view the type of table storage engine in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.