mysql数字常量的字符集_第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 使用规则：

字符集

排序规则

字符串确认规则

指定

指定

按照指定确认

指定

缺失

按照指定字符集对应的默认排序规则确认

缺失

指定

按照 @@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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！