mysql 排序规则 字符集_我如何找出我的MySQL中的默认字符集/排序规则

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?

解决方案

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.