MySQL - SHOW ENGINES Statement

---

---

MySQL SHOW ENGINES Statement

The SHOW ENGINES statement returns the current status information of the storage engines of MySQL. It displays the following details −

• Engine − Name of the engine

• Support − Type of support, which can be YES, DEFAULT, NO and, DISABLED.

• Comment − Description about the engine

• Transactions − A Boolean value specifying whether the engine supports transactions.

• XA − A Boolean value specifying whether the engine supports XA transactions.

• Savepoints − A Boolean value specifying whether the engine supports savepoints.

Syntax

Following is the MySQL SHOW ENGINES statement −

 SHOW [STORAGE] ENGINES 

Example

You can retrieve the information about the engines using the SHOW ENGINES statement as shown below −

 SHOW ENGINES\G; 

Output

Following is the output of the above query −

 *************** 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.00 sec) 

Example

You can also specify STORAGE in the middle of the statement as −

 SHOW STORAGE ENGINES; 

Output

The above query produces the following output −

Engine	Support	Comment	Transactions	XA	Savepoints
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL
PERFORMANCE_ SCHEMA	YES	Performance Schema	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO