How do I see what character set a MySQL database / table / column is?
725,650
Solution 1
Here's how I'd do it -
For Schemas (or Databases - they are synonyms):
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";
For Tables:
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "schemaname"
AND T.table_name = "tablename";
For Columns:
SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
AND table_name = "tablename"
AND column_name = "columnname";
Solution 2
For columns:
SHOW FULL COLUMNS FROM table_name;
Solution 3
For databases:
USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";
Cf. this page. And check out the MySQL manual
Solution 4
For all the databases you have on the server:
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Output:
+----------------------------+---------+--------------------+
| database | charset | collation |
+----------------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| my_database | latin1 | latin1_swedish_ci |
...
+----------------------------+---------+--------------------+
For a single Database:
mysql> USE my_database;
mysql> show variables like "character_set_database";
Output:
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| character_set_database | latin1 |
+----------------------------+---------+
Getting the collation for Tables:
mysql> USE my_database;
mysql> SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
OR - will output the complete SQL for create table:
mysql> show create table my_tablename
Getting the collation of columns:
mysql> SHOW FULL COLUMNS FROM my_tablename;
output:
+---------+--------------+--------------------+ ....
| field | type | collation |
+---------+--------------+--------------------+ ....
| id | int(10) | (NULL) |
| key | varchar(255) | latin1_swedish_ci |
| value | varchar(255) | latin1_swedish_ci |
+---------+--------------+--------------------+ ....
Solution 5
For tables:
SHOW TABLE STATUS
will list all the tables.
Filter using:
SHOW TABLE STATUS where name like 'table_123';
Comments
-
Amandasaurus almost 2 years
What is the (default) charset for:
MySQL database
MySQL table
MySQL column