{{ it.name }}
{{ it.text }}
[_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 使用规则:
-- 正确的转换
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 @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;
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 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
-- 原有排序结果
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 字句指标为 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)