一次由于MySQL字符比较规则引发的问题

一次由于MySQL字符比较规则引发的问题

困而学,学而知

问题回顾

用一个关于停车卡的业务,停车卡的信息包括:卡号车牌号用户名有效期等,用户可以编辑停车卡的车牌号用户名。现在有两张表,停车卡表: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的命名是有规则的。

  • 比较规则名称以与其关联的字符集的名称开头。
  • 后面紧跟着比较规则主要作用于那种语言
  • 名称后缀意味着该比较规则是否区分语言中的重音、大小写等。
后缀英文释义描述
_aiaccent insensitive不区分重音
_asaccent sensitive区分重音
_cicase insensitive不区分大小写
_cscase sensitive区分大小写
_binbinary以二进制方式比较

换一个角度来解决本次问题

上面的表格,我们可以肯定会想到将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;

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://baozi.fun/2019/11/10/一次由于MySQL字符比较规则引发的问题

Buy me a cup of coffee ☕.