How to check if MySQL table is UTF-8 and has storageEngine InnoDB?
21,362
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";
Author by
GeekedOut
Recently started working on http://www.problemio.com Thank you to all the people on StackOverflow who have helped me. Could not have made the progress I did without your help!! EDIT: I LOOOOOOOOOOVE STACKOVERFLOW. THANK YOU TO ALL THE AMAZING PEOPLE HERE!!!!
Updated on May 18, 2020Comments
-
GeekedOut almost 4 years
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?
-
GeekedOut almost 13 yearsIt makes sense, but are you sure that is the syntax? I get this error: Unknown column 'users' in 'field list'
-
Nicola Cossu almost 13 yearsHi. 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?
-
GeekedOut almost 13 yearsI was trying the first query. But I'll keep hacking around with it
-
GeekedOut almost 13 yearsOh 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!
-
Nicola Cossu almost 13 yearsGlad I could help you. Bye :)