困而学,学而知
问题回顾
用一个关于停车卡的业务,停车卡的信息包括:卡号
、车牌号
、用户名
和有效期
等,用户可以编辑停车卡的车牌号
和用户名
。现在有两张表,停车卡表: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;