专栏首页爱可生开源社区第07期:有关 MySQL 字符集的 SQL 语句

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

本篇为理清字符集的续篇(上一篇:第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 使用规则:

二、字符集转换函数

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

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

不过有两点不同:

1)参数 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)

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

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 个。数字指标越小,优先级越高。以下为指标值说明:

同时,为了便于大家理解,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 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

本文分享自微信公众号 - 爱可生开源社区(ActiontechOSS),作者:杨涛涛

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 第06期:梳理 MySQL 字符集的相关概念

    用途:用来映射简单的单字节字符,比如大小写英文字母、阿拉伯数字、常用的标点符、运算符、控制字符等。

    爱可生开源社区
  • 技术分享 | kubernetes 环境测试部署 MySQL 的随想

    爱可生研发团队成员,负责数据库管理平台相关项目的开发和故障排查,好奇 MySQL 技术原理及各类数据库实现方案。

    爱可生开源社区
  • 第09问:MySQL 莫名崩溃,如何保留现场?

    一旦遇到生产环境上的 MySQL 崩溃,就需要保留现场信息,供分析用。虽然 MySQL 的 error log 中会打印部分信息,但对于比较隐蔽的崩溃,往往显得...

    爱可生开源社区
  • linux下vim的配置

    二狗不要跑
  • 开发神器Vim配置

    1.在用户根目录建立文件.vimrc【点击下载完整.vimrc】 “.vimrc文件内容如下 set hlsearch                  “高亮...

    苦咖啡
  • vim中set的用法

    生信编程日常
  • Python的内置函数(三十四)、set()

    set() 函数创建一个无序不重复元素集,可进行关系测试,删除重复数据,还可以计算交集、差集、并集等。

    于小勇
  • 变量类型-Set

    教程:     集合是一个无序不重复元素的序列,它的特点是:有的可变,有的不可变,元素无序不可重复! 一:集合的创建     (1)使用花括号      ...

    py3study
  • java集合的操作(set,Iterator)

    Iterator、Collection、Set和HashSet关系  Iterator<——Collection<——Set<——HashSet  Iterat...

    用户3030674
  • 常用vim设置

        \ if line("'\"")>0&&line("'\"")<=line("$") |

    一见

扫码关注云+社区

领取腾讯云代金券