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

Question

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:

Check what character encoding a table has?
Check what storage engine a table uses?
Check if all tables are certain encoding?
Check if all tables have a certain storage engine?

This may be useful: http://stackoverflow.com/questions/4515490/how-do-i-know-if-a-mysql-table-is-using-myisam-or-innodb-engine/4515794#4515794 — sarnold, May 23 '11 at 22:10

score 36 · Accepted Answer · edited May 25 '17 at 23:27

36

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";

edited May 25 '17 at 23:27

Kirby

15,127
10
89
104

answered May 23 '11 at 22:09

Nicola Cossu

54,599
15
92
98

It makes sense, but are you sure that is the syntax? I get this error: Unknown column 'users' in 'field list' – GeekedOut May 23 '11 at 22:19
Hi. Yes, I'm sure. It's a simple query on the information_schema that works with mysql >= 5.0. What are you talking about, my first or second query? – Nicola Cossu May 23 '11 at 22:22
I was trying the first query. But I'll keep hacking around with it – GeekedOut May 23 '11 at 22:32
Oh I know what I was doing wrong..I was plugging in the actual table name in the select clause of the first query lol....ok all works super nicely - thank you! – GeekedOut May 23 '11 at 22:37
Glad I could help you. Bye :) – Nicola Cossu May 23 '11 at 22:49

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

1 Answers1

Linked