How can I check MySQL engine type for a specific database?

How can I check MySQL engine type for a specific database via my开发者_运维技巧sql query?

In MySQL, databases don't have an engine type; tables have engine types. The Friendly Manual even explicitly states:

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

You can query the information_schema database (substitute your database name and table name in the example):

SELECT `ENGINE` FROM `information_schema`.`TABLES`
  WHERE `TABLE_SCHEMA`='your_database_name' AND `TABLE_NAME`='your_table_name';

You can also get the value of the global environment variable storage_engine - which is only used as a default when a table is created without an engine specified, it does not affect the server in any other way:

SHOW GLOBAL VARIABLES LIKE 'storage_engine'

Databases do not have engines. Tables have. You can run e.g. SHOW TABLE STATUS:

SHOW TABLE STATUS FROM mydatabase

Available engines can be found with SHOW ENGINES.

Tip: if you are the using the official command-line client rather than GUI tools you might want to use the \G command (not to be confused with lowercase \g):

Send the current statement to the server to be executed and display the result using vertical format.

... which turns this:

mysql> SHOW TABLE STATUS;
+----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+-----------
---+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----
------------------------------------------------------+
| Name                             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_leng
th | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comm
ent                                                   |
+----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+-----------
---+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----
------------------------------------------------------+
| canal                            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |
 0 |  27262976 |              1 | 2015-04-10 11:07:01 | NULL        | NULL       | utf8_general_ci   |     NULL |                |

… into this:

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: canal
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 27262976
 Auto_increment: 1
    Create_time: 2015-04-10 11:07:01
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: cliente
         Engine: InnoDB
[…]

SHOW TABLE STATUS retrieves that kind of stuff, see the MySQL docs.

Use this command:

SHOW ENGINES\G;  

Your default engine will show as Support: DEFAULT

the list of engines used by databases:

SELECT TABLE_SCHEMA, ENGINE FROM information_schema.TABLES group by TABLE_SCHEMA, ENGINE order by TABLE_SCHEMA, ENGINE;