第07期：有关 MySQL 字符集的 SQL 语句

作者: 爱可生开源社区 | 来源:发表于2020-06-24 16:39 被阅读0次

本篇为理清字符集的续篇（上一篇：第06期：梳理 MySQL 字符集的相关概念），重点讲述字符集涉及到的 sql 语句用法。

一、character introducer

翻译过来就是字符引导。也就是针对字符串，显式的给定一个字符编码和排序规则，不受系统参数的影响。

语法很简单：

[_charset_name]'string' [COLLATE collation_name]

示例：

字符串 "北京加油❤!"

-- 字符集 utf8mb4，排序规则 utf8mb4_bin
select _utf8mb4 "北京加油❤!" collate utf8mb4_bin as result;
+------------------+
| result           |
+------------------+
| 北京加油❤!       |
+------------------+
1 row in set (0.00 sec)


-- 字符集 utf8mb4,collate 字句缺失，此时对应排序规则为utf8mb4_w0900_ai_ci
select _utf8mb4 "北京加油❤!" as result;
+------------------+
| result           |
+------------------+
| 北京加油❤!       |
+------------------+
1 row in set (0.00 sec)

-- 字符集缺失，此时字符集按照参数 @@character_set_connection 值来指定。
mysql> select "北京加油❤!" collate gb18030_chinese_ci as result;
ERROR 1253 (42000): COLLATION 'gb18030_chinese_ci' is not valid for CHARACTER SET 'utf8mb4'

-- 查看变量 @@character_set_connection，确认其字符集不包含排序规则 gb18030_chinese_ci，所以以上语句报错。
mysql> select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

-- 那给下正确的排序规则 utf8mb4_bin，执行正确。

mysql> select "北京加油❤!" collate utf8mb4_bin as result;
+------------------+
| result           |
+------------------+
| 北京加油❤!       |
+------------------+
1 row in set (0.00 sec)


-- 字符集和排序规则都不指定，此时字符串对应的字符集和排序规则和参数 @@character_set_connection 一致。
select "北京加油❤!" as result;
-- 那这条语句其实被 MySQL 解释为
select _utf8mb4 "北京加油❤!" collate utf8mb4_0900_ai_ci as result;

总结 Introducer 使用规则：

字符集	排序规则	字符串确认规则
指定	指定	按照指定确认
指定	缺失	按照指定字符集对应的默认排序规则确认
缺失	指定	按照 @@character_set_connection 的值确认，排序规则与字符集不匹配则报错
缺失	缺失	按照 @@character_set_connection 的值确认

二、字符集转换函数

1. convert 函数

convert 函数类似于 introducer，不过只能指定字符集。

举个例子，通过 convert 函数转换字符串 "北京加油❤!" 的编码为 utf8mb4。不过前提是转换前后字符集一定要兼容。

-- 正确的转换
mysql> select convert("北京加油❤!" using utf8mb4) ;
+-------------------------------------------+
| convert("北京加油❤!" using utf8mb4)       |
+-------------------------------------------+
| 北京加油❤!                                |
+-------------------------------------------+
1 row in set (0.00 sec)

-- 错误的转换，字符集编码不兼容。

mysql> select convert("北京加油❤!" using latin1) ;
+------------------------------------------+
| convert("北京加油❤!" using latin1)       |
+------------------------------------------+
| ?????!                                   |
+------------------------------------------+
1 row in set (0.00 sec)

2. charset 函数

检测字符串的字符集。可以检测出当前字符串在当前 session 的字符集。

mysql> set @a="北京加油❤!";
Query OK, 0 rows affected (0.00 sec)

mysql> select charset(@a);
+-------------+
| charset(@a) |
+-------------+
| utf8        |
+-------------+
1 row in set (0.00 sec)

3. set names 语句

语法为：

SET NAMES {'charset_name'
[COLLATE 'collation_name'] | DEFAULT}

这条语句最常用，可是也最容易被滥用，比如语句

set names latin1 collate latin1_bin;

执行后会默认执行一系列语句，也就是把非服务端的相关参数给重新设定了。

set session character_set_results = latin1;

set session character_set_client = latin1;

set session character_set_connection=latin1;  

set session collation_connection = latin1_bin;

那现在重新执行确认一下，跟 introducer 一样，没有指定 collate 语句，默认为字符集对应的排序规则。

mysql> set names latin1 ;
Query OK, 0 rows affected (0.00 sec)

-- 那这里看到相关参数值全部被改了。

mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_results','character_set_client');
+--------------------------+-------------------+
| VARIABLE_NAME            | VARIABLE_VALUE    |
+--------------------------+-------------------+
| character_set_client     | latin1            |
| character_set_connection | latin1            |
| character_set_results    | latin1            |
| collation_connection     | latin1_swedish_ci |
+----------------------------------------------+

那如果想改回默认值，简单执行：

mysql> set names default;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_results','character_set_client');
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_results    | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
4 rows in set (0.00 sec)

不过有一点要注意的是，并不是所有字符集都适用于这条语句，比如定长字符集 utf32，设置就会报错。因为变量 @@character_set_client 不支持这个字符集。

mysql> set names utf32;
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'utf32'

4. set character set 语句

语法为：

SET {CHARACTER SET | CHARSET}
{'charset_name' | DEFAULT}

类似语句 set names，同样是设置以下三个 session 参数：

character_set_results
character_set_client
character_set_connection

同样是可以恢复默认值，还有同样的限制规则等。

不过有两点不同：

参数 character_set_connection 的值不会被设定为指定的字符集，而是继承参数 character_set_database 所设定的字符集。

示例：

mysql> set character set latin1;
Query OK, 0 rows affected (0.01 sec)

-- 检索结果显示，参数 character_set_connection 的值和 character_set_database 的值一致。
mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_database','character_set_results','character_set_client');
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_results    | latin1             |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

只用来设置字符集，不能定义具体的排序规则名称，也就是排序规则名称都是字符集对应的默认排序规则名称。这点从以上例子就可以看出来。

5. collate 子句

collate 语句强制指定排序规则，优先级最高。也就是显式指定collate 会覆盖已有的排序规则。

这里涉及到单个字符串以及字符串拼接的排序规则问题。

显式的指定排序方式

-- 示例表 c1,
mysql> create table c1 (n char(1));
Query OK, 0 rows affected (0.06 sec)

-- 插入示例数据，英文大小写字母乱序插入
mysql> insert into c1 with recursive a(x,y) as 
(
   select 65,97 union all select x+1,y+1 from a where x<90
) 
select char(x using ascii) x from a 
union all 
select char(y using ascii) y from a order by rand();

Query OK, 52 rows affected (0.02 sec)
Records: 52  Duplicates: 0  Warnings: 0

改变 order by 的排序规则。

-- 原有排序结果
mysql> select n from c1 order by n  desc limit 6;
+------+
| n    |
+------+
| Z    |
| z    |
| y    |
| Y    |
| x    |
| X    |
+------+
6 rows in set (0.00 sec)

-- collate 显式指定后，排序结果。
mysql> select n from c1 order by n  collate utf8mb4_0900_bin desc limit 6;
+------+
| n    |
+------+
| z    |
| y    |
| x    |
| w    |
| v    |
| u    |
+------+
6 rows in set (0.00 sec)

用于具体的列别名

mysql> select n collate utf8mb4_bin as n from c1 order by n  desc limit 6;
+------+
| n    |
+------+
| z    |
| y    |
| x    |
| w    |
| v    |
| u    |
+------+
6 rows in set (0.01 sec)

用于聚合函数

mysql> select max(n) n from c1;
+------+
| n    |
+------+
| Z    |
+------+
1 row in set (0.00 sec)

-- 强制collate结果
mysql> select max(n collate utf8mb4_bin) n from c1;
+------+
| n    |
+------+
| z    |
+------+
1 row in set (0.00 sec)

或者用于 where，group by，having 等等。

用于统计排序方式的强制性指标

比如要在 where 条件里过滤字符串，where a = 'a'，那此时是用 a 的排序规则，还是字符 'a' 的排序规则，这里就涉及到一个排序规则的强制性指标。

MySQL 的排序规则强制性指标值从 0 到 6 一共 7 个。数字指标越小，优先级越高。以下为指标值说明：

值	说明
0	显式指定 collate 语句，最高优先级
1	两个不同排序规则的字符串拼接，此时指标为 1
2	两个不同的列、存储过程参数或者本地变量的指标为 2
3	系统常量的指标为 3。比如 user(),version() 等函数
4	简单的字符串文字，指标为 4
5	数字或者日期等指标为 5
6	NULL 或者包含 NULL 的表达式或者结果为 NULL 的指标都为 6

同时，为了便于大家理解，MySQL 提供了如何检测这个指标的函数 coercibility，举几个例子看下：

示例：

-- collate 字句指标为 0
mysql> select coercibility('a' collate utf8mb4_bin) as 'coercibility';
+--------------+
| coercibility |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

-- 简单的文本指标为 4
mysql> select coercibility('a') as 'coercibility';
+--------------+
| coercibility |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

-- 日期指标为 5
mysql> select coercibility(now()) as 'coercibility';
+--------------+
| coercibility |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

-- 系统常量指标为 3
mysql> select coercibility(@@server_uuid) as 'coercibility';
+--------------+
| coercibility |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

总结

对于字符集的控制相关 SQL 就介绍的差不多了。主要举例介绍了 MySQL 字符集相关的处理 SQL 语句，比如设置客户端相关编码语句：SET NAMES/SET CHARSET；设置排序规则语句：COLLATE；给 MySQL 信号的 introducer 等。希望对大家有帮助。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

第07期：有关 MySQL 字符集的 SQL 语句

一、character introducer

示例：

总结 Introducer 使用规则：

二、字符集转换函数

1. convert 函数

2. charset 函数

3. set names 语句

4. set character set 语句

示例：

5. collate 子句

显式的指定排序方式

用于统计排序方式的强制性指标

示例：

总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SQL调优