MySQL SHOW ENGINES statement

Overview

In this tutorial, we will learn about MySQL SHOW ENGINES statement. This command is used to see what storage engines are available on the MySQL Server.

https://www.testingdocs.com/mysql-storage-engines/

Syntax

The general syntax of the statement is as follows:

mysql> SHOW ENGINES;

This command displays the output in a tabular format.

mysql> SHOW ENGINES \G

The statement displays the storage engines that are compiled into the MySQL server and whether
they are available at runtime.

Examples

The command with \G terminator displays the output in the row format.

mysql> SHOW ENGINES \G

*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.01 sec)

MySQL SHOW ENGINES

The Support column can have values like:

YES
NO
DISABLED
DEFAULT

The value in the Support column is YES or NO, to indicate that the storage engine is or is not available.
DISABLED if the engine is present but turned off, or DEFAULT for the storage engine that the server
uses by default. The InnoDB storage engine is the default engine in the above example.

—