MySQL知识中的小透明字符集和比较规则

2023年 11月 21日 51.6k 0

背景

微信限制公众号推送消息夹带“私货”

image.png

取完快递,缴完停车费后,,我们常常会收到夹带“私货”的公众号消息,备注里面充斥着大量的广告类消息,往往比实际推送的有效消息还要长~

好在,微信今年针对此类现象进行了治理。不对夹带“私货”进行批判,只聊一下“私货”里面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 ('😄');

发现并不能成功插入

image.png

数据库版本升级,势在必行

Oracle宣布停止维护 MySQL5.7 了,截止时间是2023.10.31

image.png

对我们有什么影响吗?

虽然我们用的是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表情了
image.png

字符集和比较规则操作

查看字符集

SHOW CHARSET
SHOW CHARACTER SET

image.png

 

常见的有

字符集 最大长度
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 {字符集}

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论