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

Question

What is the (default) charset for:

• MySQL database

• MySQL table

• MySQL column

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

Answer 2

For columns:

SHOW FULL COLUMNS FROM table_name;

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

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

Answer 5

For tables:

SHOW TABLE STATUS will list all the tables.

Filter using:

SHOW TABLE STATUS where name like 'table_123';

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;

Answer 7

For databases:

Just use these commands:

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

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
;

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.

Answer 10

For tables and columns:

show create table your_table_name

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> 

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

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'

Answer 14

For database : USE db_name; SELECT @@character_set_database;

Answer 15

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

Answer 16

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

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'