本篇为理清字符集的续篇（上一篇：第06期：梳理 MySQL 字符集的相关概念），重点讲述字符集涉及到的 sql 语句用法。
一、character introducer
翻译过来就是字符引导。也就是针对字符串，显式的给定一个字符编码和排序规则，不受系统参数的影响。
语法很简单：
[] 'string' [COLLATE collation_name]示例：
-- 字符集 utf8mb4，排序规则 utf8mb4_binselect _utf8mb4 "北京加油!" collate utf8mb4_bin as result;+------------------+| result |+------------------+| 北京加油! |+------------------+1 row in set (0.00 sec)-- 字符集 utf8mb4,collate 字句缺失，此时对应排序规则为utf8mb4_w0900_ai_ciselect _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 使用规则：
二、字符集转换函数
-- 正确的转换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 函数
mysql> set ="北京加油!";Query OK, 0 rows affected (0.00 sec)mysql> select charset();+-------------+| charset() |+-------------+| 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;
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)
mysql> set names utf32;ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'utf32'
SET {CHARACTER SET | CHARSET}{'charset_name' | DEFAULT}
character_set_results
character_set_client
character_set_connection
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）只用来设置字符集，不能定义具体的排序规则名称，也就是排序规则名称都是字符集对应的默认排序规则名称。这点从以上例子就可以看出来。
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 aunion allselect char(y using ascii) y from a order by rand();Query OK, 52 rows affected (0.02 sec)Records: 52 Duplicates: 0 Warnings: 0
-- 原有排序结果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)
-- collate 字句指标为 0mysql> select coercibility('a' collate utf8mb4_bin) as 'coercibility';+--------------+| coercibility |+--------------+| 0 |+--------------+1 row in set (0.00 sec)-- 简单的文本指标为 4mysql> select coercibility('a') as 'coercibility';+--------------+| coercibility |+--------------+| 4 |+--------------+1 row in set (0.00 sec)-- 日期指标为 5mysql> select coercibility(now()) as 'coercibility';+--------------+| coercibility |+--------------+| 5 |+--------------+1 row in set (0.00 sec)-- 系统常量指标为 3mysql> select coercibility(@) as 'coercibility';+--------------+| coercibility |+--------------+| 3 |+--------------+1 row in set (0.00 sec)
总结
对于字符集的控制相关 SQL 就介绍的差不多了。主要举例介绍了 MySQL 字符集相关的处理 SQL 语句，比如设置客户端相关编码语句：SET NAMES/SET CHARSET；设置排序规则语句：COLLATE；给 MySQL 信号的 introducer 等。希望对大家有帮助。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
