背景
微信限制公众号推送消息夹带“私货”
取完快递,缴完停车费后,,我们常常会收到夹带“私货”的公众号消息,备注里面充斥着大量的广告类消息,往往比实际推送的有效消息还要长~
好在,微信今年针对此类现象进行了治理。不对夹带“私货”进行批判,只聊一下“私货”里面emoji表情是如何存储的
我们使用MySQL5.7,建一张表,专门存储emoji表情
CREATE TABLE `emoji` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
'title' varchar(120) NOT NULL DEFAULT '',
PRIMARY KEY(id)
) ENGINE = InnoDB;
尝试插入数据
INSERT INFO emoji(title) VALUES ('😄');
发现并不能成功插入
数据库版本升级,势在必行
Oracle宣布停止维护 MySQL5.7 了,截止时间是2023.10.31
对我们有什么影响吗?
虽然我们用的是5.7版本,但是使用的是RDS云服务啊,跟我们自己服务没关系啊~
其实不是的,Oracle不维护5.7版本了,站在云服务角度来看,存在一些问题:
- 开源数据库存在未知安全漏洞,MySQL5.7得安全性无法保障
- 开源数据库需遵守开源协议,基于源软件规定的任何修改都需要履行开源义务,若自己维护MySQL5.7很可能会让自己修改的代码被动开源
各大云厂商也陆续推出了对于MySQL5.7的维护截止时间,那么,在不久的将来,我们即将面对的一个问题是 -- MySQL的版本迁移, 很可能是迁移至8.0版本
没区分大小写,查到的太多
在比较常见的模糊查询需求中,通常的解决方法是 like '%{key}%' 一把梭,但一旦给模糊查询增加一个限制条件:区分大小写,这时候会发现这一套突然玩不转了,当我们搜索key包含a的所有数据时,会同时返回key包含A的所有数据。
除了在代码中根据大小写过滤返回结果外,可以直接通过修改数据表的比较规则来实现模糊查询的大小写区分
字符集合比较规则
我们不常关注到字符集合比较规则,但他们确实能帮我们解决一些实际问题
字符集
计算机中的字符,全都会以二进制进行存储,想要存储字符串,就需要建立<字符,二进制>的映射关系。将字符映射为二进制的过程,叫做编码;将二进制数据映射为字符的过程,叫做解码
常用的字符集
- ASCII 字符集
-
- 收录128个字符,使用1个字节进行编码
- ISO 8859-1 (latin1)字符集
-
- 收录256个字符,在ASCII上,扩充了128个西欧常用字符,也可以用一个字节来编码
- GB2312 字符集
-
- 收录汉字、拉丁字母、希腊字母、日文、俄语。其中收录汉字6763个,其他文字符号682个,同时兼容ASCII字符集,使用变长编码方式:若字符在ASCII字符集中,使用1字节编码,否则采用2字节编码
- GBK 字符集
-
- 在收录字符范围上扩充了GB2312字符集,兼容GB2312
- utf8 字符集
-
- 收录地球上能想到的所有字符(不断扩充)。使用变长编码方式,编码一个字符,需要1~4个字节
- utf8是Unicode字符集的一种编码方案,还有utf16、utf32这几种编码方案
MySQL支持的字符集和比较规则
将数据直接迁移至8.0版本的实例,是否就能直接用了呢?
很可能出现因字符集问题,导致的数据问题,MySQL5.7的默认字符集为utf8,MySQL8.0的默认字符集也是utf8,但MySQL5.7的utf8,实际为utf8mb3,MySQL8.0的utf8,实际为utf8mb4,utf8mb3表示最多使用3个字节来表示一个字符,utf8mb4表示最多用4个字节表示一个字符。
这里假设MySQL5.7中创建的表都没有制定字符集,默认使用了utf8mb3,如果在MySQL8.0不指定字符集创建数据表,默认使用ut8mb4,用新建的表和MySQL5.7迁移过来的表做关联查询,则关联字段无法走索引,执行效率受到影响。
utf8可以用1 ~ 4个字节表示一个字符,常用的字符使用1~3个字节就可以表示了,MySQL针对utf8定义了两个新的字符集概念:
- utfmb3,用1~3个字节表示字符,在MySQL中,utf8是utfmb3的别名
- utfmb4,用1~4个字节表示字符
回到文初的问题,如果要正常存储emoji表情,那么就需要修改对应的字符集编码
修改编码后,果然能正常写入emoji表情了
字符集和比较规则操作
查看字符集
SHOW CHARSET
SHOW CHARACTER SET
常见的有
字符集 | 最大长度 |
---|---|
ascii | 1 |
latin1 | 1 |
gb2312 | 2 |
gbk | 2 |
utf8 | 3 |
utf8mb4 | 4 |
查看比较规则
SHOW COLLATION LIKE {字符集};
每种字符集,都支持多种比较规则,以utf8为例
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_spanish_ci作用于西班牙语,常见的utf8_general_ci是通用的规则
- 后缀表示重音、是否区分大小写
后缀 | 英文释义 | 描述 |
---|---|---|
_ai | accent insensitive | 不区分重音 |
_as | accent sensitive | 区分重音 |
_ci | case insensitive | 不区分大小写 |
_cs | case sensitive | 区分大小写 |
_bin | binary | 以二进制方式比较 |
utf8默认的比较规则utf8_general_ci就是不区分大小写的
我们在模糊搜索时,如果想区分大小写,又不想修改代码,就可以修改一下数据库对应列的比较规则
了解数据库字段区分大小写了,数据库表名、库名如何区分大小写呢?
比如常用的定时任务框架Quartz,需要建一堆的初始数据表,copy下来的建表语句表名全都是大写的,如果大小写敏感,那么SQL里Select的小写表名就找不到
通过lower_case_table_names数据库参数,决定数据表是否大小写敏感,参数有3个取值
- 0 大小写敏感 (LInux默认)按照大小写生成数据库文件,命名为AbC,则生成AbC.frm文件
- 1 大小写不敏感 (Windows默认)数据库表转换为小写存储,SQL表名也会转成小写查询
- 2 大小写不敏感 (OS X默认)数据库表原样保存,SQL语句将库名转成小写
字符集和比较规则级别
在MySQL中,字符集和比较规则,分成几个级别:服务器级别 -> 数据库级别 -> 表级别 -> 列级别,如果没有显示标识,则继承上一级别的字符集和比较规则
服务器级别
MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则,如果要修改这两个变量,需要重启server
- character_set_server
- collation_server
数据库级别
创库时设置、修改已有库的字符串和比较规则,如果不设置,则默认使用服务器级别的字符集和比较规则
CREATE DATABASE test
CHARACTER SET {字符集名称}
COLLATE {比较规则名称}
ALTER DATABASE test
CHARACTER SET {字符集名称}
COLLATE {比较规则名称}
查看数据库的字符集和比较规则
USE {DATABASE名};
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
表级别
创表时设置、修改已有表的字符串和比较规则,如果不设置,则默认使用数据库级别的字符集和比较规则
CREATE TABLE {表名} (列信息)
CHARACTER SET {字符集名称}
COLLATE {比较规则名称}
ALTER TABLE {表名}
CHARACTER SET {字符集名称}
COLLATE {比较规则名称}
列级别
同一张表中,不同的列,可以有不同的字符集和比较规则,若没有单独设置,则默认使用表的字符集和比较规则
CREATE TABLE {表名} (
{列名} {字符串类型} CHARACTER SET utf8 COLLATE utf8_general_ci,
其他列信息...
);
可以单独修改某一列的字符集和比较规则,如果修改前的字符,无法用修改后的字符集表示,会出错(ex:utf8存汉字,转换为ascii会报错,ascii只支持128个字符表示)
ALTER TABLE {表名} MODIFY col VARCHAR(10) CHARATER SET utf8 COLLATE gbk_chinese)ci;
通信中的字符集
客户端和服务端通信过程中,涉及到编码转换
- character_set_client
- 服务端认为客户端使用character_set_client编码,所以客户端编码需要和设置的character_set_client编码保持一致
- character_set_results
- 服务端将结果集用character_set_results编码后发送回客户端
- character_set_connection
- 服务端的一种中间态编码,用来将character_set_client转为character_set_results
大部分情况下我们只需要用utf8编码一把梭,不需要关注MySQL是如何进行编码转换的,MySQL也提供了一把梭的方法,统一设置三个字符变量的值
SET NAMES {字符集}