Collation作为MySQL基础，你知道多少

Collation后缀

• 非binary的collation，可以不显示指定 _ai 或 _as，_ci会同时隐含_ai，_cs会同时隐含_as。如：latin1_general_ci和latin1_general_ai_ci是一样的。
• utf8mb4_0900_ai_ci 中的 0900：Unicode的字符集的Collation需要指定UCA（Unicode Collation Algorithm）版本号, 0900即是这个版本号；如果没有指定，则会使用 version-4.0.0 UCA，如 utf8mb4_unicode_ci。

MySQL Server的Charset和Collation

查看MySQL Server支持的Charset

有很多种方法可以查看当前MySQL Server支持的Character Set:

show character set; -- 方法1

show charset; -- 方法2

show char set; -- 方法3

select * from information_schema.character_sets; -- 方法 4

查看特定字符集信息（主要包含默认的Collation和MAXLEN）:

show character set like 'utf%';; -- 方法1

show charset like 'utf%';; -- 方法2

show char set like 'utf%'; -- 方法3

select * from information_schema.character_sets 
where CHARACTER_SET_NAME like 'utf%'; -- 方法 4

查看MySQL Server支持的Collation

SHOW COLLATION WHERE Charset = 'utf8mb4';

或者

select * from INFORMATION_SCHEMA.COLLATIONS where CHARACTER_SET_NAME='utf8mb4';

查看MySQL Server当前的Charset和Collation

show variables like 'character_set_server';
show variables like 'collation_server';

或者使用语句：

select @@character_set_server, @@collation_server;

MySQL Server默认的Charset和Collation

MySQL官方文档可查看默认的Charset和Collation：

• <=5.7 doc MySQL Server 5.7或之前版本默认的Charset和Collation是： latin1 和 latin1_swedish_ci
• 8.x doc MySQL Server 8.x（当前版本）默认的Charset和Collation是： utf8mb4 和 utf8mb4_0900_ai_ci

修改MySQL Server默认的Charset和Collation

修改默认值，需要重新编译源码。

cmake . -DDEFAULT_CHARSET=latin1

或者

cmake . -DDEFAULT_CHARSET=latin1 \
  -DDEFAULT_COLLATION=latin1_german1_ci

指定MySQL Server的Charset和Collation

可以在启动MySQL Server的时候指定Server的Charset和Collation，下面三种方法是等效的：

mysqld # 默认的charset是utf8mb4, 而且 utf8mb4 默认的collation是 utf8mb4_0900_ai_ci

或

mysqld --character-set-server=utf8mb4

或

mysqld --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_0900_ai_ci

数据库(Database)的Charset和Collation

创建数据库的时候，如果没有指定character set 和 collation，会自动用MySQL Server的 character set和collation。

查看数据的Character Set和Collation

USE db_name;
SELECT @@character_set_database, @@collation_database;

不想改变当前数据库的话，可以使用语句：

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

指定或修改数据库(Database)的Character Set和Collation

创建db时指定：

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

修改：

ALTER DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

表(Table)的Charset和Collation

如果创建表的时候没有指定表级别Charset和Collation，会默认使用数据库(Database)的Charset和Collation。

查看表(Table)的Charset和Collation

SELECT 
t.TABLE_SCHEMA,
t.table_name,
ccsa.* 
FROM 
information_schema.`TABLES` t,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` ccsa
WHERE ccsa.collation_name = t.table_collation
AND t.table_schema = "db_name"
AND t.table_name = "table_name";

指定或修改表(Table)的Character Set和Collation

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

列(Column)的Charset和Collation

如果创建表的时候没有指定列的Charset和Collation，会默认使用表(Table)的Charset和Collation。

查看列(Column)的Charset和Collation

SELECT 
*
FROM information_schema.`COLUMNS`
WHERE table_schema = "db_name"
AND table_name = "table_name";

指定或修改列(Column)的Character Set和Collation

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

Connection Character Sets and Collations

Client和MySQL Server交互前，先建立连接(Connection)。

Client通过建立的connection发送SQL Statements（查询、插入等操作）到MySQL Server；MySQL Server则通过建立的connection返回给Client相应的结果（SQL执行结果，或者错误信息）。

Client和Server建立连接并设置character set

1. Client在建立连接的时候会指定collation (charset的默认collation)；
2. MySQL Server根据collation找到对应的charset；
3. MySQL Server然后charset设置session变量: character_set_client, character_set_results, character_set_connection，collation_connection被设置为指定charset的默认collation。

相关session变量

• character_set_server 和 collation_server： MySQL Server的Character Set 和 Collation；
• character_set_database 和 collation_database：数据库的Character Set 和 Collation；
• character_set_client：MySQL Server使用此session变量的charset作为client 发送来的SQL Statements的charset；
• character_set_connection： Server会把client发送的statements字符集从character_set_client转为character_set_connection；
• collation_connection：这个对字符串常量的比较很重要；
• character_set_results：Server返回给client的结果（column values, result metadata such as column names, and error messages）对应的字符集；

查看connection相关的session变量：

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

设置character set和collation

SET NAMES {'charset_name'
    [COLLATE 'collation_name'] | DEFAULT}

SET NAME 会设置三个session变量（session system variables）为指定的charset和collation：

• character_set_client
• character_set_connection
• character_set_results

SET {CHARACTER SET | CHARSET}
    {'charset_name' | DEFAULT}

SET CHARACTER SET会设置 character_set_client 和 character_set_results为指定的charset；

并把 character_set_connection 设置为 character_set_database 的charset。