How can I figure out the default charset/collation in my MySQL

Question

I use MySQL and MySQL Workbench 8.0+ on Ubuntu 18.04. When I create a column with a string type, I leave the charset/collation to default. In MySQL workbench, it just show me that is it Default Charset.

What is the default charset used by my current MySQL and Workbench? How can I figure this out?

Answer 1

Use SHOW CREATE TABLE. For example:

CREATE TABLE a (
    dflt VARCHAR(11),
    cs   VARCHAR(11)  CHARACTER SET latin1,
    cola VARCHAR(11)  COLLATE utf8mb4_hungarian_ci,
    cc   VARCHAR(11)  CHARACTER SET latin1  COLLATE latin1_bin,
    colb VARCHAR(11)  COLLATE latin1_bin
);

SHOW CREATE TABLE a\G

mysql> SHOW CREATE TABLE a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `dflt` varchar(11) DEFAULT NULL,
  `cs`   varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `cola` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_hungarian_ci DEFAULT NULL,
  `cc`   varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `colb` varchar(11) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Notes:

• Most of the columns have the charset and collation spelled out.
• The one that does not (dflt) inherits those settings from the DEFAULTs for the table.
• Each CHARACTER SET has a "default" collation.
• Each COLLATE is associated with exactly one CHARACTER SET, namely the first part of the collation name.
• (Not shown here): The DEFAULTs for the table are inherited from the DATABASE.
• MySQL 8 defaults to CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci if you do nothing.
• Older versions defaulted to CHARSET=latin1 COLLATE=latin1_swedish_ci
• The "0900" or "520" in some collations refers to Unicode Standards versions 9.0 and 5.20. You can infer from this that there may be new, "better", collations in the future.