MySQL: Get character-set of database or table or column?

Question

What is the (default) charset for:

• MySQL database

• MySQL table

• MySQL column

Twilley

For tables and columns, simply use SHOW CREATE TABLE, as discussed in #57628717 – Sherasherar

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

Omdurman

Should be noted that information_schema is only in MySQL 5 onwards I believe. – Rebatement

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 – Cleavland

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%'; – Buttock

the table operation returns "Empty set (0.00 sec)" for me – Kimberykimble

Answer 2

For columns:

SHOW FULL COLUMNS FROM table_name;

Erund

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). – Portion

@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. – Epigenous

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). – Dimmer

Answer 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

Tucky

This only answers 1/3 of the question. – Duwe

@TobyJ, I don't see you complaining at https://mcmap.net/q/53822/-mysql-get-character-set-of-database-or-table-or-column – Epigenous

what does cf stands for? link does not exist though. @Epigenous – Arredondo

This should be merged in the top answer. It really helped me. – Fourpenny

@YannisDran: "cf." is an abbreviation for Latin "confer" = "bring together" = "compare". It is often used more generally to mean "see". – Entoblast

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

Imidazole

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 – Quintessa

My output shows type as the data type as well @MTK, perhaps above is a copy paste error in the "output:" section. – Agate

Answer 5

For tables:

SHOW TABLE STATUS will list all the tables.

Filter using:

SHOW TABLE STATUS where name like 'table_123';

Rebatement

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. – Eliott

@Daan, Stop spreading misinformation. See #1050228 , with the collation you can tell the charset. – Epigenous

Answer 6

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;

Playmate

Like this! As per: MySQL Docs – Dania

Answer 7

For databases:

Just use these commands:

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

Corycorybant

This is the answer from the official mysql doc. dev.mysql.com/doc/refman/8.0/en/charset-database.html – Cartwright

Answer 8

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
;

Hilaria

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 :) – Rusticus

@JerryKrinock You get every columns of the current database and nothing if no database is selected. – Jackanapes

Answer 9

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.

Hochstetler

in mysql databases can have default character sets – Neuritis

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. – Epigenous

I used SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '<database-name>' \G; And it worked great :) Thanks! – Greatcoat

Answer 10

For tables and columns:

show create table your_table_name

Neuritis

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. – Neuritis

Also, if the column doesn't state a particular charset, then it is using the table's default charset. – Epigenous

Answer 11

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> 

Pileum

Isn't this a repeat of the first answer on top and https://mcmap.net/q/53822/-mysql-get-character-set-of-database-or-table-or-column ? – Epigenous

@Epigenous did you actually compare my answer to these properly? – Pileum

Answer 12

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

Heddle

Citation needed for "default character set/collation is always LATIN" in MySQL. – Epigenous

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. – Ability

Answer 13

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'

Alpheus

Answer 14

For database : USE db_name; SELECT @@character_set_database;

Bullwhip

Answer 15

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

Gobbledegook

Answer 16

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

Stuccowork

Answer 17

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

in "information_schema" this path

"COLUMNS"->about columns

"TABLES"->about table

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'

Thereinafter

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

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