前言

最近遇到一些问题，业务上在通过查询指定某些公司名称的字段，名称中有全角和半角的括号名称，由于排序规则不同，查询出来的结果也会有不同。所以就些问题做了一些总结，方便以供同样遇到此类问题的同学进行参考。

字符集与排序规则的概念

字符集

字符集是一组抽象的字符（Charcter）组合的集合。举一个例子，所有的汉字就算一个“字符集合”， 所有的英语字母也算一个“字符集合”。 注意，我这里说它们是字符集合，而且还有双引号。是因为字符集并不简单的是字符的集合， 准确概述来说，字符集是一套符号和编码的规则。

字符集需要以某种字符编码方式来表示、存储字符。我们知道，计算机内部，所有信息最终都是一个二进制值。每一个二进制位（bit）有0和1两种状态。而如果用不同的0和1组合表示不同的字符就是编码。

字符集与字符编码

字符集是书写系统字母与符号的集合，而字符编码则是将字符映射为一特定的字节或字节序列，是一种规则。通常特定的字符集采用特定的编码方式（例如：ASCII、IOS-8859-1、GB2312、GBK，都是即表示了字符集又表示了对应的字符编码），因此基本上可以将两者视为同义词。

排序规则

是指对指定字符集下不同字符的比较规则。

在MySQL 中，每种字符集都对应多种排序规则，所以可以分别设置字符集和排序规则。而在SQL Server 排序规则和字符集一起设定的。

MySQL 支持字符集

MySQL 支持的字符集查看，以及字符集默认的排序规则。

show character set;

MySQL 支持的排序规则

查看MySQL 查看的排序规则

CS ：区分大小写

CI：不区会大小写

AS：区分重音

AI：不区分重音

bin：根据每个位模式进行排序比较，即区分大小也，也区分重音。

SHOW COLLATION;

字符集与排序规则应用的优先级

数据库级别

参数：character_set_server

默认值：utf8mb4

默认数据库使用此字符集。

创建数据库指定字符集

CREATE DATABASE db10 DEFAULT CHARACTER SET latin7 COLLATE latin7_general_cs;

说明：

1. 不指定排序规则，将使用字符集默认排序规则。
2. 默认排序规则，在使用show create database 不会被显示。

表级别

CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

说明：

1.不指定字符集和排序规则将继承数据库的。

字段级别

CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB;

优先级

从大到小：字段 > 表 > 数据库

常用的几种字符集

1. ascii：美国信息交换标准代码，使用的128个字符。占用一个字节。
2. big5：大五码，使用的是繁体中文。最多占用两个字节。
3. gb18030：包含gb2312和gbk，支持简体中文，繁体中文和少数民族文字。最多4个字节。
4. gb2312：支持简体中文，最多2个字节。
5. gbk：支持简体中文，繁体中文，最多2个字节。
6. latin1：Latin1是ISO-8859-1的别名，除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。占用一个字节。
7. utf8(utf8mb3)：是针对Unicode的一种可变长度字符编码。支持 拉丁文、希腊文、西里尔字母、亚美尼亚语、希伯来文、阿拉伯文、叙利亚文， 中日韩文字、东南亚文字、中东文字等。最多3字节。
8. utf8mb4：除了包含utf8 以外，还支持 Emoji 表情。最多4字节。

字符集使用不当引起的问题

索引问题

当进行关联查询时，两个相关联的字段，字符集不一样，可能存在隐式转换，或者直接不走索引，从而影响性能。

示例：

创建表

CREATE TABLE `t1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `org_id` varchar(60) NOT NULL,
  `user_id` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_org_id_user_id` (`org_id`,`user_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 CREATE TABLE `t2` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `c_user_id` varchar(60) NOT NULL,
  `type` tinyint NOT NULL,
  `user_id` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c_user_id_type` (`c_user_id`,`type`),
  KEY `idx_user_id_type` (`user_id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询

explain SELECT *
FROM 
    (SELECT *
    FROM t1
    WHERE `org_id` = '625e8603f70a663212e3148b') o
LEFT JOIN `t2` b
    ON o.`user_id` = b.`c_user_id`
    AND b.`type` = 1;

将字段类型都改成utf8mb4，再看执行计划

全角和半角

全角和半角主要和排序规则有关系

示例：

创建两个表

分别使用utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 排序规则

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20),
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_0900_ai_ci;
CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20),
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

插入数据

insert into t1(name)values('北京(某某)公司'),('北京（某某）公司');
insert into t2(name)values('北京(某某)公司'),('北京（某某）公司');

验证排查规则带来的差异

mysql> select * from t1 where name = '北京(某某)公司';
+----+--------------------------+
| id | name                     |
+----+--------------------------+
|  1 | 北京(某某)公司           |
|  2 | 北京（某某）公司         |
+----+--------------------------+
2 rows in set (0.03 sec)
mysql> select * from t2 where name = '北京(某某)公司';
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | 北京(某某)公司       |
+----+----------------------+
1 row in set (0.01 sec)

原因

utf8mb4_0900_ai_ci：支持一下些扩展的功能，所以会兼容一些问题。

utf8mb4_general_ci：仅仅是字符之间一一比较。速度要比utf8mb4_0900_ai_ci快。

所以就会有上面的查询不同的现象。

乱码

示例：

设置一种不支持中文的字符集

set names latin1;

说明

1. 此命令是会话级别的操作。
2. 将 character_set_client，character_set_connection，character_set_results三个会话级别的参数都设置成latin1

验证

mysql> select * from t2 where name = '北京(某某)公司';
Empty set (0.01 sec)
mysql> select * from t2;
+------+----------+
| id   | name     |
+------+----------+
| NULL | ??(??)?? |
| NULL | ???????? |
+------+----------+
2 rows in set (0.00 sec)

说明

1. 此时使用正确的name 已经无法检索到匹配的记录。
2. 通过列出全表记录，已经成为乱码。
3. 原因就是配置的字符集不一致辞，导致出现问题。

尾部空格

示例：

插入数据

insert into t1(name)values('北京(某某)公司 '),('北京（某某）公司 ');
insert into t2(name)values('北京(某某)公司 '),('北京（某某）公司 ');

验证

mysql> select * from t1 where name = '北京(某某)公司 ';
+----+---------------------------+
| id | name                      |
+----+---------------------------+
|  3 | 北京(某某)公司            |
|  4 | 北京（某某）公司          |
+----+---------------------------+
2 rows in set (0.00 sec)
mysql> select * from t2 where name = '北京(某某)公司 ';
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  1 | 北京(某某)公司        |
|  3 | 北京(某某)公司        |
+----+-----------------------+
2 rows in set (0.01 sec)

说明

1. t1的结果说明

1. 1. 排序规则是 utf8mb4_0900_ai_ci
   2. 是兼容全角和半角
   3. 此排序规则，尾部不会填充空白。
   4. 所以结果是b和c 的组合策略，最终会得到第三和第四条记录。

1. t2 的结果说明

1. 1. 排序规则是utf8mb4_general_ci
   2. 不兼容全角和半角，所以只能得到半角记录。
   3. 此排序规则，尾部有填充空白，所以无法区分真实的字符是否有空白。
   4. 最终得到第一和第三条记录。

参考

https://dev.mysql.com/doc/refman/8.0/en/charset.html

https://learn.microsoft.com/zh-cn/sql/relational-databases/collations/collation-and-unicode-support?redirectedfrom=MSDN&view=sql-server-ver16