关于GreatSQL字符集的总结

马玉玲 · 发表于 2023-12-20 15:57:18

关于GreatSQL字符集的总结

前言

最近的SQL优化工作中经常遇到因字符集或校验规则不一致导致索引使用不了的问题，修改表的字符集或校验规则相当于把表重构，表中数据量大时，处理起来费时费力，希望应用开发者在设计之初时注意到此问题，让后期接手运维的小伙伴少一些负担。GreatSQL的字符集和校验规则种类繁多，提供灵活性的同时，也带来使用混乱的烦恼。本文对字符集做一个总结，让读者对GreatSQL的字符集有一个全面的了解。
一、字符集介绍

计算机存储的都是二进制数据，十进制数字可以转换为二进制，那么字符串要转换为二进制数据就需要一个映射关系，字符转换为二进制叫编码，二进制转换为字符叫解码，这个其实就是字符集的概念，描述某个字符范围的编码规则，不同的字符集包含的字符范围不一样。
下面介绍一些重要的字符集。
1. ASCII字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。不可见字符主要指控制字符（比如换行、回车等）和通信字符（比如文头SOH、文尾EOT等）等。ASCII字符集总共128个字符，可以使用1个字节来进行编码。
2. ISO8859-1字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符（包括德法两国的字母）。ISO8859-1字符集也可以使用1个字节来进行编码。这个字符集还有一个别名Latin1。
3. GB2312字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。收录汉字6763个，收录其他文字符号682个。这种字符集同时兼容ASCII字符集，所以如果字符在ASCII字符集中则采用1字节编码，否则采用2字节编码。
4. GBK字符集

GBK对GB2312字符集进行了扩充，编码方式兼容GB2312字符集。同GB2312字符集，如果字符在ASCII字符集中则采用一字节编码，否则采用2字节编码。
5. UTF-8字符集

几乎收录了当今世界各个国家地区使用的字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1~4个字节。通常一个汉字是3个字节编码，一个字母是一个字节编码。UTF-8是Unicode的一种编码方案，此外还有UTF-16、UTF-32编码方案。
我们可以看出，ISO8859-1、GB2312、GBK、UTF-8字符集都包含了ASCIIS字符集，GBK包含了GB2312字符集, UTF-8收录的字符涵盖GBK、GB2312、ISO8859-1的字符，但是它们编码规则是不一样的。
比如汉字“我”的编码方式：
GBK中的编码：1100111011010010
UTF-8的编码：111001101000100010010001
二、GreatSQL字符集与比较规则

1.查看GreatSQL支持的字符集

通过语句show (character set|charset) [like 匹配的模式] 来查看支持的字符集,数据来源于表information_schema.character_sets。
这其中character set与charset 是同义词。

greatsql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

复制代码

上面为GreatSQL8.0的41种字符集， Default collation是指字符集默认的比较规则，Maxlen指字符集最多需要几个字节来表示一个字符。
GreatSQL5.7版本里的utf8指的是utf8mb3,是“阉割”过的UTF-8字符集，只使用1~3个字节表示字符。 utf8mb4是正宗的UTF-8字符集，使用1~4个字节表示字符，utf8mb4比utf8mb3多存储一些字符，比如emoj表情等。Mysql已经在很大程度上优化了utf8mb4字符集性能，是默认的字符集。
2.查看GreatSQL比较规则

可以通过语句show collation [like 匹配的模式] 来查看支持的比较规则，数据来源于表information_schema.collations
每种字符集都有若干种比较规则。查询一下utf8mb4的比较规则。

greatsql> show collation like 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bg_0900_ai_ci | utf8mb4 | 318 | | Yes | 0 | NO PAD |
| utf8mb4_bg_0900_as_cs | utf8mb4 | 319 | | Yes | 0 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
| utf8mb4_bs_0900_ai_ci | utf8mb4 | 316 | | Yes | 0 | NO PAD |
| utf8mb4_bs_0900_as_cs | utf8mb4 | 317 | | Yes | 0 | NO PAD |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD |
| utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE |
| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD |
| utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD |
| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD |
| utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD |
| utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD |
| utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE |
| utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD |
| utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD |
| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD |
| utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD |
| utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD |
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE |
| utf8mb4_gl_0900_ai_ci | utf8mb4 | 320 | | Yes | 0 | NO PAD |
| utf8mb4_gl_0900_as_cs | utf8mb4 | 321 | | Yes | 0 | NO PAD |
| utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD |
| utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE |
| utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD |
| utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE |
| utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD |
| utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD |
| utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD |
| utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE |
| utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD |
| utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE |
| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD |
| utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD |
| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD |
| utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD |
| utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4 | 322 | | Yes | 0 | NO PAD |
| utf8mb4_mn_cyrl_0900_as_cs | utf8mb4 | 323 | | Yes | 0 | NO PAD |
| utf8mb4_nb_0900_ai_ci | utf8mb4 | 310 | | Yes | 0 | NO PAD |
| utf8mb4_nb_0900_as_cs | utf8mb4 | 311 | | Yes | 0 | NO PAD |
| utf8mb4_nn_0900_ai_ci | utf8mb4 | 312 | | Yes | 0 | NO PAD |
| utf8mb4_nn_0900_as_cs | utf8mb4 | 313 | | Yes | 0 | NO PAD |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE |
| utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD |
| utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE |
| utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD |
| utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD |
| utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD |
| utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE |
| utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD |
| utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE |
| utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD |
| utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE |
| utf8mb4_sr_latn_0900_ai_ci | utf8mb4 | 314 | | Yes | 0 | NO PAD |
| utf8mb4_sr_latn_0900_as_cs | utf8mb4 | 315 | | Yes | 0 | NO PAD |
| utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD |
| utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE |
| utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD |
| utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE |
| utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD |
| utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD |
| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD |
+----------------------------+---------+-----+---------+----------+---------+---------------+
89 rows in set (0.00 sec)

复制代码

当前查询版本为GreatSQL8.0.32，这里utf8mb4比较规则有89种。Collation的命名规则也有规律，以字符集的名字开头，后面有尾缀_ai,_as,_ci,_cs,_bin。这几个尾缀的含义如下：
尾缀英文含义中文描述_aiaccent insensitive不区分重音_asaccent sensitive区分重音_cicase insensitive不区分大小写_cscase sensitive区分大小写_binbinary以二进制方式比较utf8mb4字符集默认的校验规则是utf8mb4_0900_ai_ci,这个0900表示基于Unicode Collation Algorithm（UCA)9.0.0的排序规则。
校对规则都有一个Pad_attribute属性，取值为 PAD SPACE或NO PAD，PAD SPACE的校对规则会忽略结尾的空格字符，NO PAD相反。
举例说明：从上面的查询中我们可以看到utf8mb4_0900_bin 的Pad_attribute为NO PAD，表示不忽略结尾的空格。utf8mb4_bin的Pad_attribute为PAD SPACE，表示会忽略结尾的空格。

greatsql> create table t1(id int ,c1 varchar(20)) character set utf8mb4 collate utf8mb4_0900_bin;
Query OK, 0 rows affected (0.02 sec)
greatsql> insert into t1 values(1,'abc ');
Query OK, 1 row affected (0.00 sec)
greatsql> select * from t1 where c1='abc';
Empty set (0.00 sec)
greatsql> select * from t1 where c1='abc ';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
--c1列校验规则为utf8mb4_0900_bin，插入时尾部带一个空格，查询时需要严格匹配空格字符才能查到数据。
下面修改c1校验规则为utf8mb4_bin,查询条件中带不带空格都能查询到数据。
greatsql> alter table t1 modify c1 varchar(20) character set utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
greatsql> select * from t1 where c1='abc';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
greatsql> select * from t1 where c1='abc ';
+------+------+
| id | c1 |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)

复制代码

3.字符集和比较规则的使用级别。

GreatSQL字符集与比较规则使用级别，分别为服务器级别、数据库级别、表级别、列级别。
(1)服务器级别（server）：
提供两个系统变量来表示。character_set_server与collation_server。启动服务器程序时通过启动选项或程序运行中通过set 语句进行修改这两个变量。比如配置文件中配置：

[server]
character_set_server=utf8mb4
collation_server=utf8mb4_bin

复制代码

（2）数据库级别（database）：
提供两个系统变量来表示。character_set_database与collation_database, 这两个变量不能直接修改的，只能通过创建库或修改库语句来修改。创建库语句不指定字符集将使用服务器级别的设置。
对应的语句：

create/alter database 数据库名 [character set 字符集名称][collate 比较规则名称] ;
例如：
create database test character set utf8mb4 collate utf8mb4_0900_bin;
alter database test character set utf8mb4 collate utf8mb4_0900_ai_ci;

复制代码

级别用法数据库级别(database)表级别create table table_name(列信息)[character set 字符集名称][collate 比较规则名称];alter table table_name[character set 字符集名称][collate 比较规则名称];列级别create table table_name(列名字符串类型 [character set 字符集名称] [collate 比较规则名称]，其他列...);
alter table table_name modify 列名字符串类型 [character set 字符集名称] [collate 比较规则名称];修改列信息时注意：修改列时不指定字符集，即使创建时指定了也会使用表的字符集和比较规则。如果修改后的列字符集不能表示列中存储的数据，则会报错。(3)表级别：
创建和修改表时指定。同一个database里的不同表可以使用不同字符集。创建表的语句中没有指明字符集和比较规则，则使用该表所在数据库的字符集与比较规则。
对应的语句：

create table table_name(列信息)
[character set 字符集名称][collate 比较规则名称];
alter table table_name
[character set 字符集名称][collate 比较规则名称];
例如：
create table t1(id int,c1 varchar(30)) character set utf8mb4 collate utf8mb4_0900_bin;
alter table t1 character set utf8 collate utf8mb4_0900_ai_ci;

复制代码

这里注意一下，字符集和比较规则是相互关联的，如果在修改时仅指定了字符集，那么比较规则也会随之变为修改后的字符集默认的比较规则。如果仅指定了比较规则，那么字符集也会变为比较规则对应的字符集。这么说来的话，只需要指定比较规则就够了。
修改表的默认字符集只对后面添加的列有效，已存在的列的校验规则保持不变。
(4)列级别：
对于存储字符串的列，同一个表中不同列可以有不同的字符集和比较规则。可以在创建和修改列信息时指定该列的字符集与比较规则。如果创建和修改列时不指定，则使用该表的字符集与比较规则。
对应的语句：

create table table_name(列名字符串类型 [character set 字符集名称] [collate 比较规则名称]，其他列...);
alter table table_name modify 列名字符串类型 [character set 字符集名称] [collate 比较规则名称];

复制代码

修改列信息时注意：修改列时不指定字符集，即使创建时指定了字符集，也会使用表的字符集和比较规则。如果修改后的列字符集不能表示列中存储的数据，则会报错。
4.客户端和服务器端通信过程中使用的字符集。

字符串在计算机中就是一个二进制字节序列，如果编码和解码使用不同的字符集，那最后得到的结果肯定是你不认识的乱码。
如果客户端和服务器端使用的字符集是不同的，那么就会涉及一个字符集转换的过程。从用户角度看，客户端发送请求以及服务器返回的响应都是字符串。从机器的角度看，客户端发送的请求和服务器返回的响应本质上就是一个字节序列，在这个“客户端发送请求、服务器返回响应”的过程中，其中经历了多次字符集转换。
我们以linux系统为例来说明一下这个过程。
a. 客户端发送请求。
一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。Linux系统的LANG环境变量决定了操作系统当前使用的哪种字符集。

[root@greatdb-1 ~]# echo $LANG
en_US.UTF-8

复制代码

如果启动客户端程序时指定了选项 default-character-set,则客户端使用此选项指定的字符集。
b. 服务器接收请求
从本质上说服务器接收到的请求就是一个二进制字节序列。服务器怎么对这个字节序列进行解码，或者说服务器解码使用什么样的字符集，这取决于系统变量character_set_client，此变量为session级别的。客户端编码实际使用的是什么字符集，服务器不知道，服务器就以系统变量chacter_set_client设置的字符集进行解码接收到的请求。一般情况下我们要尽量保证这两个字符集是一致的，否则就会出现鸡同鸭讲的事情。
c. 服务器处理请求
服务器真正处理请求时，又会将请求的字节序列转换为系统变量character_set_connection对应的字符集进行编码的字节序列,同时还有配套的系统变量collation_connection来表示这些字符串应该使用哪种比较规则。那么为什么还要做这种转换呢，我认为这样设计充分体现了它的灵活性。以下面的例子为例：
客户端发出请求：select 'a'='A'; 这个应该返回true还是false呢？

greatsql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
greatsql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
greatsql> set collation_connection=utf8mb4_0900_bin;
Query OK, 0 rows affected (0.00 sec)
greatsql> show variables like '%colla%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_bin |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
greatsql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

复制代码

从上面的例子可以看出，开始比较规则collation_connection='utf8mb4_0900_ai_ci'，对大小写字母不敏感，'a'='A'是返回true的，修改比较规则collation_connection='utf8mb4_0900_bin'后，按二进制比较，'a'='A'是返回false的。
那如果传递一个字面量与表中列的存储值进行比较呢？

greatsql> show variables like 'collation_connection';
+----------------------+------------------+
| Variable_name | Value |
+----------------------+------------------+
| collation_connection | utf8mb4_0900_bin |
+----------------------+------------------+
1 row in set (0.01 sec)
greatsql> create table t0(name varchar(20) character set utf8mb4 collate utf8mb4_0900_ai_ci);
Query OK, 0 rows affected (0.02 sec)
greatsql> insert into t0 values('a');
Query OK, 1 row affected (0.02 sec)
greatsql> insert into t0 values('A');
Query OK, 1 row affected (0.01 sec)
greatsql> select * from t0 where name='A';
+------+
| name |
+------+
| a |
| A |
+------+
2 rows in set (0.00 sec)

复制代码

从上面的例子可以看出列的字符集与比较规则比系统变量collation_connection的优先级要高，比较时会把请求中的字符串转换为列的字符集，按照列的比较规则去比较。
d. 服务器生成响应。
服务器以什么样的字符集编码的字节序列发送到客户端呢，这取决于系统变量character_set_results的值。还以上面建的t0表为例。

greatsql> insert into t0 values('我');
Query OK, 1 row affected (0.01 sec)
greatsql> select * from t0;
+------+
| name |
+------+
| a |
| A |
| 我 |
+------+
3 rows in set (0.00 sec)
greatsql> show variables like 'character_set_result';
Empty set (0.00 sec)
greatsql> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)
greatsql> set character_set_results=ascii;
Query OK, 0 rows affected (0.00 sec)
greatsql> select * from t0;
+------+
| name |
+------+
| a |
| A |
| ? |
+------+
3 rows in set (0.00 sec)
greatsql> select * from t0 where name='我';
+------+
| name |
+------+
| ? |
+------+
1 row in set (0.00 sec)

复制代码

从上面的例子可以看出，开始character_set_results设置的utf8mb4,可以正常显示汉字”我”,当修改为ascii字符集时，汉字以？代替，ascii字符集不包含汉字，无法表示汉字。就是说服务器生成响应的会转换为character_set_results设定的字符集的字节序列发送到客户端。
e. 客户端接收响应
客户端接收到的响应也是一个字节序列，再将这个字节序列按照操作系统当前使用的字符集来解释这个字节序列，如果指定了启动选项default-character-set，则以这个选项的设置解码收到的字节序列，显示成人类能看懂的字符。
通过上面介绍的过程，我们可以看出在客户端与服务器的通信过程中是经过了多次字符集转换的，我们以一个表格来总结一下这个转换过程。
客户端发送请求字符串按照操作系统当前使用的字符集或客户端程序启动选项default-character-set指定的字符集进行编码服务器接收请求服务器认为请求是按照character_set_client变量设置的字符集编码的，因此按照这个变量设置的字符集进行解码。服务器处理请求把请求字节序列从character_set_client字符集转换为character_set_connection字符集，配套collation_connection作为比较规则进行处理。处理数据库列中数据时，以列的字符集和比较规则进行处理，如果character_set_connection与之不一致，将会再被转换为列的字符集。服务器生成响应服务器采用character_set_results设置的字符集编码的字节序列发送给客户端。客户端接收响应客户端收到的响应字节序列，再按照操作系统当前使用的字符集或客户端程序启动选项default-character-set指定的字符集进行解释，显示成人类能看懂的字符。注意这3个系统变量character_set_client、character_set_connection、character_set_results，都是session级别的系统变量。在连接服务器时，客户端将默认的字符集信息与用户名、密码等信息一起发送给服务器，服务器收到后会将这3个系统变量初始化为客户端的默认字符集。这几个变量可以使用set 语句进行修改，但无特殊需要也没有必要去修改。要注意，使用set语句修改这三个系统变量，并不会改变客户端在编码请求字符串时使用的字符集，也不会修改客户端默认的字符集。
使用总结

GreatSQL 8.0版本，character_set_server默认为utf8mb4，collation_server默认为utf8mb4_0900_ai_ci。这个是全局级别的默认设置。创建database不指定字符集与校验规则，会使用character_set_server与collation_server参数指定的字符集与校验规则。注意一点如果指定character set而不指定 collate，则collate会取指定字符集的default collation，而不是collation_server指定的校验规则。
创建表时不指定字符集与校验规则，会使用所属数据库的字符集与校验规则，注意一点如果指定character set而不指定collate，则collate会取指定字符集的default collation，而不是数据库的校验规则。
创建表时，列上不指定字符集与校验规则，会使用表上指定的字符集与校验规则，表上也没有指定，那就是使用所属database的字符集与校验规则。
切记如果只指定字符集，但是不指定校验规则，校验规则会取指定字符集的default collation，有可能结果不符合预期。所以创建数据库，数据表，表上的列，最好不要只指定字符集。可以字符集与校验规则都省略，也可以只指定校验规则。
一定要保证关联表上关联字段的字符集与校验规则保持一致，才能很好的使用索引。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17915828.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！