mysqlutf-8innodbstorage-engines

How to check if MySQL table is UTF-8 and has storageEngine InnoDB?


Googling around just finds instructions for changing from one format to another, but I can't seem to find how exactly to make sure which of these I have first.

How can I:

  1. Check what character encoding a table has?
  2. Check what storage engine a table uses?
  3. Check if all tables are certain encoding?
  4. Check if all tables have a certain storage engine?

Solution

  • You can use information_schema in order to know the engine of each table.

    select table_name,engine 
    from information_schema.tables
    where table_schema = 'your_database'
    

    For the encoding you can use

    show create table table_name
    

    or, even better

    select 
    c.character_set_name 
    from information_schema.tables as t,
         information_schema.collation_character_set_applicability as c
    where c.collation_name = t.table_collation
    and t.table_schema = "your_db"
    and t.table_name = "table_name";