Determining Which Storage Engines the Server Supports
Problem
You want to know whether you can create a table using a given storage engine.
Solution
Use the SHOW
ENGINES
statement to ask the server which storage engines it
supports.
Discussion
The SHOW
ENGINES
statement provides information about
which storage engines the server supports. Its output looks like
this:
mysql>SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
...
The Engine
value indicates a
storage engine name and the Support
value indicates its status. If Support
is YES
or DEFAULT
, the engine is available. If the
value is NO
or DISABLED
, the engine is not
available. The following Ruby method uses those rules to determine
engine status and return a list of the engines that are
available:
def get_storage_engines(dbh) engines = [] dbh.select_all("SHOW ENGINES").each do |engine, support| engines << engine if ["YES", "DEFAULT"].include?(support.upcase) ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.