818

What is the (default) charset for:

  • MySQL database

  • MySQL table

  • MySQL column

Improve this question
1

17 Answers 17

Reset to default
960

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

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 = "mydatabasename"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "mydatabasename"
  AND table_name = "tablename"
  AND column_name = "columnname";
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Should be noted that information_schema is only in MySQL 5 onwards I believe.
As far as I can tell the closest you can get to retrieving column specific character set information in MySQL < 5 is to do SHOW FULL COLUMNS FROM tableName
This answer was very helpful, but if you want to trouble shoot a character_set / collation issue you would probably also need to check connection character_set, client_character_set etc... : SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';
the table operation returns "Empty set (0.00 sec)" for me
it seems that COLLATION_CHARACTER_SET_APPLICABILITY is available starting with 5.6
545

For columns:

SHOW FULL COLUMNS FROM table_name;
Improve this answer

3 Comments

Hello, this is the future speaking! For anyone checking this answer, this method only shows Collation, rather than charset. I believe this changed at MySQL 5. (See answer with more points for a better method).
@fideloper, With the collation you can tell the charset. That is because the first part of collation includes the charset, e.g. if the collation is latin1_swedish_ci, the charset can't be anything else besides latin1. If the collation is utf8mb4_general_ci, the charset can't be anything else besides utf8mb4.
This tells you the table character set. It doesn't tell you the character set of tables created in the future when no character set is specified in the create table syntax (you'll need the schema character set for that).
242

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

Improve this answer

5 Comments

This only answers 1/3 of the question.
@TobyJ, I don't see you complaining at stackoverflow.com/a/4805510/632951
what does cf stands for? link does not exist though. @Pacerier
This should be merged in the top answer. It really helped me.
@YannisDran: "cf." is an abbreviation for Latin "confer" = "bring together" = "compare". It is often used more generally to mean "see".
200

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  |
+---------+--------------+--------------------+ ....
Improve this answer

2 Comments

What version of mysql show this output for type ? I have mysql 5.7.9 and type show the data type of the column not the character set. Some like int(10) varchar(255) ... etc and not utf8
My output shows type as the data type as well @MTK, perhaps above is a copy paste error in the "output:" section.
75

For tables:

SHOW TABLE STATUS will list all the tables.

Filter using:

SHOW TABLE STATUS where name like 'table_123';
Improve this answer

2 Comments

Please note. The collation shown in the show table status is not the character set of the table. The collation tells you how the characters are sorted / compared. e.g. utf8_bin_ci compares data without regarding the case (case insensitive, so "m" and "M" are the same), utf8_bin_cs compares with case sensitivity (so "m" and "M" are distinct). This is not the same as the character set of a table.
@Daan, Stop spreading misinformation. See stackoverflow.com/questions/1049728/… , with the collation you can tell the charset.
73

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;
Improve this answer

1 Comment

Like this! As per: MySQL Docs
45

For databases:

Just use these commands:

USE db_name;
SELECT @@character_set_database;
-- or:
-- SELECT @@collation_database;
Improve this answer

1 Comment

This is the answer from the official mysql doc. dev.mysql.com/doc/refman/8.0/en/charset-database.html
40 
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
;
Improve this answer

2 Comments

Very nice, Eric. Just paste that code into the mysql command line, hit return and you get the character set of every column in every table in every database :)
@JerryKrinock You get every columns of the current database and nothing if no database is selected.
36

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.

Improve this answer

3 Comments

in mysql databases can have default character sets
select default_character_set_name from information_schema.schemata is not enough because you can't tell which row correlate with which database. Use select default_character_set_name,schema_name from information_schema.schemata or simply: select*from information_schema.schemata.
I used SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '<database-name>' \G; And it worked great :) Thanks!
23

For tables and columns:

show create table your_table_name
Improve this answer

2 Comments

It tells you the full SQL that would be used to create the table as it currently stands, which should include it's character set.
Also, if the column doesn't state a particular charset, then it is using the table's default charset.
20

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>
Improve this answer

2 Comments

Isn't this a repeat of the first answer on top and stackoverflow.com/a/1049753/632951 ?
@Pacerier did you actually compare my answer to these properly?
16

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

Improve this answer

2 Comments

Citation needed for "default character set/collation is always LATIN" in MySQL.
Citation needed? Have you ever used a MySQL database? Everyone knows that the default character set/collation is latin1_swedish_ci because Monty Widenius, the creator of MySQL, is Swedish and was not thinking as Big Picture as he should have when he started.
5

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'
Improve this answer

Comments

4

For database : USE db_name; SELECT @@character_set_database;

Improve this answer

Comments

1

Just cheat sheet for somebody who wants to get table name, column name, and character set together for multiple tables all at once.

SELECT TABLE_NAME, COLUMN_NAME, character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "MY_DATABASE"
  AND table_name in ("tableFoo", "tableBar");

The expected output:

mysql> SELECT TABLE_NAME, COLUMN_NAME, character_set_name FROM information_schema.`COLUMNS`
    -> WHERE table_schema = "MY_DATABASE"
    ->   AND table_name in ("tableFoo", "tableBar");
+------------+---------------------+--------------------+
| TABLE_NAME | COLUMN_NAME         | character_set_name |
+------------+---------------------+--------------------+
| tableFoo   | foo1                | NULL               |
| tableFoo   | foo2                | utf8mb4            |
| tableFoo   | foo3                | NULL               |
| tableFoo   | foo4                | utf8mb4            |
| tableFoo   | foo5                | NULL               |
| tableBar   | bar1                | NULL               |
| tableBar   | bar2                | NULL               |
| tableBar   | bar3                | utf8mb4            |
| tableBar   | bar4                | NULL               |
| tableBar   | bar5                | NULL               |
| tableBar   | bar6                | NULL               |
+------------+---------------------+--------------------+
Improve this answer

Comments

0

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

Improve this answer

Comments

-2

When creating a new database, some necessary table will be generated

in "information_schema" this path

"COLUMNS"->about columns

1

"TABLES"->about table

2

For example, if you need to see all the column names and types in a table

SELECT COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_schema_name' AND TABLE_NAME = 'your_table_name'
Improve this answer

2 Comments

The answer would be easier to see if the text from the images was embedded as text in the answer.
The answer would be easier to see if the text from the images was embedded as text in the answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.