How do I see what character set a MySQL database / table / column is?

What is the (default) charset for:

• MySQL database

• MySQL table

• MySQL column

15 Answers

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

For columns:

SHOW FULL COLUMNS FROM table_name; 

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

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 | +---------+--------------+--------------------+ .... 

For tables:

SHOW TABLE STATUS will list all the tables.

Filter using:

SHOW TABLE STATUS where name like 'table_123'; 

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; 

For databases:

Just use these commands:

USE db_name; SELECT @@character_set_database; -- or: -- SELECT @@collation_database; 

 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 ; 

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.

For tables and columns:

show create table your_table_name 

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> 

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

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' 

For database : USE db_name; SELECT @@character_set_database;

show global variables where variable_name like 'character_set_%' or variable_name like 'collation%'