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 使用規則: