前言
最近遇到一些问题，业务上在通过查询指定某些公司名称的字段，名称中有全角和半角的括号名称，由于排序规则不同，查询出来的结果也会有不同。所以就些问题做了一些总结，方便以供同样遇到此类问题的同学进行参考。
字符集与排序规则的概念
字符集
字符集是一组抽象的字符（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;
说明：
- 不指定排序规则，将使用字符集默认排序规则。
- 默认排序规则，在使用show create database 不会被显示。
表级别
CREATETABLE `t1` ( `id` int DEFAULT NULL, `name` varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
说明：
1.不指定字符集和排序规则将继承数据库的。
字段级别
CREATETABLE `t1` ( `id` int DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL) ENGINE=InnoDB;
优先级
从大到小：字段 > 表 > 数据库
常用的几种字符集
- ascii：美国信息交换标准代码，使用的128个字符。占用一个字节。
- big5：大五码，使用的是繁体中文。最多占用两个字节。
- gb18030：包含gb2312和gbk，支持简体中文，繁体中文和少数民族文字。最多4个字节。
- gb2312：支持简体中文，最多2个字节。
- gbk：支持简体中文，繁体中文，最多2个字节。
- latin1：Latin1是ISO-8859-1的别名，除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。占用一个字节。
- utf8(utf8mb3)：是针对Unicode的一种可变长度字符编码。支持 拉丁文、希腊文、西里尔字母、亚美尼亚语、希伯来文、阿拉伯文、叙利亚文， 中日韩文字、东南亚文字、中东文字等。最多3字节。
- utf8mb4：除了包含utf8 以外，还支持 Emoji 表情。最多4字节。
字符集使用不当引起的问题
索引问题
当进行关联查询时，两个相关联的字段，字符集不一样，可能存在隐式转换，或者直接不走索引，从而影响性能。
示例：
创建表
CREATETABLE `t1` ( `id` bigintNOTNULL AUTO_INCREMENT, `org_id` varchar(60)NOTNULL, `user_id` varchar(60)NOTNULL, 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;CREATETABLE `t2` ( `id` bigintNOTNULL AUTO_INCREMENT, `c_user_id` varchar(60)NOTNULL, `type` tinyintNOTNULL, `user_id` varchar(60)NOTNULL, 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 排序规则
CREATETABLE `t1` ( `id` intNOTNULL AUTO_INCREMENT, `name` varchar(20), PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_0900_ai_ci;CREATETABLE `t2` ( `id` intNOTNULL AUTO_INCREMENT, `name` varchar(20), PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;
插入数据
insertinto t1(name)values('北京(某某)公司'),('北京（某某）公司');insertinto t2(name)values('北京(某某)公司'),('北京（某某）公司');
验证排查规则带来的差异
mysql>select*from t1 where name ='北京(某某)公司';+----+--------------------------+| id | name |+----+--------------------------+|1| 北京(某某)公司 ||2| 北京（某某）公司 |+----+--------------------------+2 rows inset(0.03 sec)mysql>select*from t2 where name ='北京(某某)公司';+----+----------------------+| id | name |+----+----------------------+|1| 北京(某某)公司 |+----+----------------------+1 row inset(0.01 sec)
原因
utf8mb4_0900_ai_ci：支持一下些扩展的功能，所以会兼容一些问题。
utf8mb4_general_ci：仅仅是字符之间一一比较。速度要比utf8mb4_0900_ai_ci快。
所以就会有上面的查询不同的现象。
乱码
示例：
设置一种不支持中文的字符集
set names latin1;
说明
- 此命令是会话级别的操作。
- 将 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 inset(0.00 sec)
说明
- 此时使用正确的name 已经无法检索到匹配的记录。
- 通过列出全表记录，已经成为乱码。
- 原因就是配置的字符集不一致辞，导致出现问题。
尾部空格
示例：
插入数据
insertinto t1(name)values('北京(某某)公司 '),('北京（某某）公司 ');insertinto t2(name)values('北京(某某)公司 '),('北京（某某）公司 ');
验证
mysql>select*from t1 where name ='北京(某某)公司 ';+----+---------------------------+| id | name |+----+---------------------------+|3| 北京(某某)公司 ||4| 北京（某某）公司 |+----+---------------------------+2 rows inset(0.00 sec)mysql>select*from t2 where name ='北京(某某)公司 ';+----+-----------------------+| id | name |+----+-----------------------+|1| 北京(某某)公司 ||3| 北京(某某)公司 |+----+-----------------------+2 rows inset(0.01 sec)
说明
- t1的结果说明
- 排序规则是 utf8mb4_0900_ai_ci
- 是兼容全角和半角
- 此排序规则，尾部不会填充空白。
- 所以结果是b和c 的组合策略，最终会得到第三和第四条记录。
- t2 的结果说明
- 排序规则是utf8mb4_general_ci
- 不兼容全角和半角，所以只能得到半角记录。
- 此排序规则，尾部有填充空白，所以无法区分真实的字符是否有空白。
- 最终得到第一和第三条记录。
参考
https://dev.mysql.com/doc/refman/8.0/en/charset.html
