How do I see what character set a MySQL database / table / column is?
SqlMysqlUnicodeCharacter EncodingCollationSql Problem Overview
What is the (default) charset for:
-
MySQL database
-
MySQL table
-
MySQL column
Sql Solutions
Solution 1 - Sql
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 - Sql
For columns:
SHOW FULL COLUMNS FROM table_name;
Solution 3 - Sql
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 - Sql
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 - Sql
For tables:
SHOW TABLE STATUS will list all the tables.
Filter using:
SHOW TABLE STATUS where name like 'table_123';
Solution 6 - Sql
To see default collation of the database:
USE db_name;
SELECT @@character_set_database, @@collation_database;
To see collation of the table:
SHOW TABLE STATUS where name like 'table_name';
To see collation of the columns:
SHOW FULL COLUMNS FROM table_name;
To see the default character set of a table
SHOW CREATE TABLE table_name;
Solution 7 - Sql
For databases:
Just use these commands:
USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;
Solution 8 - Sql
SELECT TABLE_SCHEMA,
TABLE_NAME,
CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
COLUMN_NAME,
COLUMN_TYPE,
C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
;
Solution 9 - Sql
I always just look at SHOW CREATE TABLE mydatabase.mytable.
For the database, it appears you need to look at SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA.
Solution 10 - Sql
For tables and columns:
show create table your_table_name
Solution 11 - Sql
For databases:
SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
Example output:
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+----------------------------+---------+--------------------+
| database | charset | collation |
+----------------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| drupal_demo1 | utf8 | utf8_general_ci |
| drupal_demo2 | utf8 | utf8_general_ci |
| drupal_demo3 | utf8 | utf8_general_ci |
| drupal_demo4 | utf8 | utf8_general_ci |
| drupal_demo5 | latin1 | latin1_swedish_ci |
...
+----------------------------+---------+--------------------+
55 rows in set (0.00 sec)
mysql>
Solution 12 - Sql
For databases:
SHOW CREATE DATABASE "DB_NAME_HERE";
In creating a Database (MySQL), default character set/collation is always LATIN, instead that you have selected a different one on initially creating your database
Solution 13 - Sql
As many wrote earlier, SHOW FULL COLUMNS should be the preferred method to get column information. What's missing is a way to get charset after that without reaching metadata tables directly:
SHOW FULL COLUMNS FROM my_table WHERE Field = 'my_field'
SHOW COLLATION WHERE Collation = 'collation_you_got'
Solution 14 - Sql
For database :
USE db_name; SELECT @@character_set_database;
Solution 15 - Sql
show global variables where variable_name like 'character_set_%' or variable_name like 'collation%'