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

Question

What is the  default  charset for    MySQL database MySQL table MySQL column

User · Answer

For databases   Just use these commands   USE db name  SELECT   character set database  -- or  -- SELECT   collation database

User · Answer

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

User · Answer

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

User · Answer

For databases   SELECT SCHEMA NAME  database   default character set name  charset   DEFAULT COLLATION NAME  collation  FROM information schema SCHEMATA    Example output   mysql gt  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 gt

User · Answer

For all the databases you have on the server   mysql gt  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 gt  USE my database  mysql gt  show variables like  character set database     Output        ---------------------------- ---------        Variable name                 Value         ---------------------------- ---------        character set database        latin1         ---------------------------- ---------      Getting the collation for Tables   mysql gt  USE my database  mysql gt  SHOW TABLE STATUS WHERE NAME LIKE  my tablename     OR - will output the complete SQL for create table   mysql gt  show create table my tablename      Getting the collation of columns   mysql gt  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     --------- -------------- --------------------

User · Answer

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

User · Answer

show global variables where variable name like  character set    or variable name like  collation

User · Answer

For columns   SHOW FULL COLUMNS FROM table name

User · Answer

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

User · Answer

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

User · Answer

For tables   SHOW TABLE STATUS will list all the tables   Filter using   SHOW TABLE STATUS where name like  table 123

User · Answer

For database   USE db name   SELECT   character set database

User · Answer

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

User · Answer

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

User · Answer

For tables and columns   show create table your table name

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

Examples related to sql

Examples related to mysql

Examples related to unicode

Examples related to character-encoding

Examples related to collation