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 使用规则: