困而学，学而知
问题回顾
用一个关于停车卡的业务，停车卡的信息包括：卡号、车牌号、用户名和有效期等，用户可以编辑停车卡的车牌号和用户名。现在有两张表，停车卡表：card和停车卡与车辆管理关联表：card_car_ref。
问题表现
用户更新了停车卡的车辆信息从AB1234改为了ab1234，然后停车卡上面就看不到车牌号了。
解决过程
最开始是没有想到用户是更新了车牌的，因为用户只是说更新了有效期（这个时候就很想要日志了）。
去数据库查看，发现这张卡有两条数据，发现这两条数据的状态都是被删除的状态，这样也就造成了停车卡不能显示车牌号了。两条数据的还有不一样的地方的就是车牌号了，这两个车牌除了大小写不一样，其他都是一样的。目前也只是意识到可能是代码的原因，然后去查看代码。代码逻辑为，先插入新车牌号（取老车牌号集合与新车牌号集合的交集），然后逻辑删除旧车牌号，也就是将旧的关联关系的状态为删除（1）。
从代码逻辑来看，应该不会有这么问题，不会造成这个问题，因为两个车牌都是不同的车牌。看到代码之后，就在线是不是在更新旧车牌号的时候，将新插入的车牌号的关联关系的状态也改了。那么，自己DEBUG，查看sql。。。果然，update的时候，将两行数据都修改了。
解决办法
由于暂时不能修改生产数据的字符集比较规则，就只改了代码，在取交集的时候，都将他们转成大写，然后再来计算。
当然了，我们今天重点不是代码，而是字符集比较规则
说说MySQL字符集
既然说到了字符集比较规则，就不得不先说说字符集这个概念。
计算机是与二进制打交道，而人却都是与字符打交道。那么为了人能够正常的与计算机打交道，就需要建立一个字符与二进制之间的关系。这就涉及字符到二进制的编码与二进制到字符的解码，以及也需要界定字符的范围。那么这个时候，我们就抽象出字符集这个概念来描述某个字符范围的编码规则。
几个重要的字符集
| 字符集 | 说明 |
|---|---|
| ASCII | 收录128个字符 |
| ISO 8859-1 | 收录256个字符，在ASCII字符集基础上扩张了128个西欧常用字符 |
| GB2312 | 收录了汉字和拉丁字母、希腊字母、日文平假名以及片假名字母、俄语西里尔字母。收录汉字6763个 |
| GBK | 对GB2312 |
| utf8 | 几乎包含了所有能想到的字符。兼容ASCII字符集，采用变长编码方式，编码一个字符集需要使用1~4个字节。 MySQL中使用阉割版的utf-8-utf8mb3(MySQL中utf8就是指的utf8mb3)，如果想要使用正宗的utf-8，请使用utf8mb4。 |
查看MySQL的字符集
mysql> show CHARSET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
.....
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
.....
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)
在上图中可以看到MySQL 的所有字符集（Charset），和字符集默认的比较规则(Default collation)。
Maxlen表示字符集中一个字符最多需要几个字节
字符集的比较规则
一般简单的字符比较的就是比较两个字符的二进制编码的大小。特殊情况比如大小写比较的话，可以将字符全部转换成大写或者小写，在比较二进制的大小。
同一种字符集可以有多种比较规则
查看utf-8的比较规则
mysql> SHOW COLLATION LIKE 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)
可以看到utf8_general_ci的是utf-8的默认比较规则。它的意思是大小写不敏感的字符比较，也是当前生产用使用的比较规则，也是造成本次生产事故的主要原因。
其实Collation的命名是有规则的。
- 比较规则名称以与其关联的字符集的名称开头。
- 后面紧跟着比较规则主要作用于那种语言
- 名称后缀意味着该比较规则是否区分语言中的重音、大小写等。
| 后缀 | 英文释义 | 描述 |
|---|---|---|
| _ai | accent insensitive | 不区分重音 |
| _as | accent sensitive | 区分重音 |
| _ci | case insensitive | 不区分大小写 |
| _cs | case sensitive | 区分大小写 |
| _bin | binary | 以二进制方式比较 |
换一个角度来解决本次问题
上面的表格，我们可以肯定会想到将utf-8的比较规则修改为utf8_general_cs就可以了，那么问题来了。我们上面查看utf-8的全部比较规则的时候，并没有看到utf8_general_cs，那就需要换一个思路了。
前面讲到字符是由二进制解码来的，那么不同的字符的二进制肯定是不一样的，那使用二进制的比较规则就可以了。
设置字符集的比较规则
MySQL有4中级别的字符集和比较规则
- 服务器级别
- 数据库级别
- 表级别
- 列级别
设置服务器级别的字符集和比较规则
在启动文件(my.cnf)中加上以下配置。
Linux环境下请使用root
[server]
character_set_server=gbk
collation_server=gbk_chinese_ci
设置数据库级别的字符集合比较规则
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称]
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称]
设置表级别的字符集和比较规则
通过建表语句或者DDL
CREATE TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]
ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]
如果没有指明，默认使用数据库级别的字符集和比较规则。
设置列级别的字符集和比较规则
同一个表中的不同的列可以又不同的字符集和比较规则
CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARSET 字符集名称] [COLLATE 比较规则名称];
如果没有指明，默认使用表级别的字符集和比较规则。
如何查看字符集和比较规则
服务器级别
-- 字符集
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)
--- 比较规则
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| collation_server | utf8mb4_unicode_ci |
+------------------+--------------------+
1 row in set (0.00 sec)
数据库级别
-- 字符集
mysql> SHOW VARIABLES LIKE 'character_set_database';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| character_set_database | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)
--- 比较规则
mysql> SHOW VARIABLES LIKE 'collation_database';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| collation_database | utf8mb4_unicode_ci |
+------------------+--------------------+
1 row in set (0.00 sec)
表级别和数据库级别
可以通过建表基于来查看
show create table 表名;
其他说明
仅修改字符集或仅修改字符规则
如果只修改字符集，比较规则也会跟着变化，如果只修改字符规则，字符集也会跟着变化。
- 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
- 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。
字符集和比较规则小结
- 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
- 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
- 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
实战：解决本文开头的问题
alter table card_car_ref modify car_num varchar(20) charset utf8mb4 collate utf8mb4_bin;
