Three ways to check the storage engine type of MySQL data tables on Linux

Introduction

MySQl mainly uses two storage engines: MyISAM and Innodb. MyISAM is non-transactional and therefore has faster reads, whereas InnoDB fully supports fine-grained transaction locking (e.g. commit/rollback). When you create a new MySQL table, you choose its type (that is, storage engine). If not selected, you will use the default engine with the preset settings.

If you want to know the type of an existing MySQL table, there are several ways to do it.

method one

If you have access to phpMyAdmin, you can find out the default database type from phpMyAdmin. Select a database from phpMyAdmin to view its table list. Under the "Type" column, you will see the data table type for each table.

Method Two

If you can log in to the MySQL server directly, another way to identify the storage engine is to log in to the MySQL server and run the following MySQL command:

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

The above command will display the engine type of the 'mytable' table in the 'mydatabase' database.

Method 3

Another way to check the engine is to use mysqlshow, which is a tool for displaying database information on the command line. mysqlshow is included in the MySQL client installation package. To use mysqlshow, you need to provide your MySQL server login credentials.

The following command will display specific database information. Under the "Engine" column, you can see the engine used by each table.

$ mysqlshow -u <mysql_user> -p -i <database></database></mysql_user>

The above is the detailed content of Three ways to check the storage engine type of MySQL data tables on Linux. For more information, please follow other related articles on the PHP Chinese website!