MySQL的函数和运算符 - 转换函数和运算符(2)

数据库杂货铺 2021-07-11

583

字符集转换

带有 USING 子句的 CONVERT() 在字符集之间转换数据：

CONVERT(expr USING transcoding_name)

在 MySQL 中，转码名称与对应的字符集名称相同。

例子：

SELECT CONVERT('test' USING utf8mb4);
SELECT CONVERT(_latin1'Müller' USING utf8mb4);
INSERT INTO utf8mb4_table (utf8mb4_column)
    SELECT CONVERT(latin1_column USING utf8mb4) FROM latin1_table;

要在字符集之间转换字符串，还可以使用 CONVERT(expr, type) 语法(不使用 USING)，或 CAST(expr AS type)，这是等价的：

CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
CAST(string AS CHAR[(N)] CHARACTER SET charset_name)

示例：

SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

如果指定如上所示的 CHARACTER SET charset_name，则结果的字符集和排序规则是 charset_name 和它默认的排序规则。如果忽略 CHARACTER SET charset_name，结果的字符集和排序规则由 character_set_connection 和 collation_connection 系统变量定义，它们决定默认的连接字符集和排序规则。

在 CONVERT() 或 CAST() 调用中不允许使用 COLLATE 子句，但可以将其应用于函数结果。例如，这些是合法的：

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;

但这些都是非法的：

SELECT CONVERT('test' USING utf8mb4 COLLATE utf8mb4_bin);
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

对于字符串字面量，指定字符集的另一种方法是使用字符集导入器。前面例子中的 _latin1 和 _latin2 是关于导入器的例子。与 CAST() 或 CONVERT() 等将字符串从一个字符集转换为另一个字符集的转换函数不同，导入器将字符串字面量指定为具有特定字符集，而不涉及转换。

用于字符串比较的字符集转换

通常，不能以不区分大小写的方式比较 BLOB 值或其他二进制字符串，因为二进制字符串使用二进制字符集，它没有具有字母大小写概念的排序规则。要执行不区分大小写的比较，首先使用 CONVERT() 或 CAST() 函数将值转换为非二进制字符串。结果字符串的比较使用它的排序规则。例如，如果转换结果排序规则不区分大小写，则 LIKE 操作不区分大小写。下面的操作就是如此，因为默认的 utf8mb4 排序规则(utf8mb4_0900_ai_ci)不区分大小写：

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4)
  FROM tbl_name;

要为转换后的字符串指定特定的排序规则，请在 CONVERT() 调用之后使用 COLLATE 子句：

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4) COLLATE utf8mb4_unicode_ci
  FROM tbl_name;

要使用不同的字符集，请在前面的语句中替换 utf8mb4 (类似地，使用不同的排序规则)。

CONVERT() 和 CAST() 可以更广泛地用于比较不同字符集中表示的字符串。例如，比较这些字符串会导致错误，因为它们有不同的字符集：

mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';
mysql> SELECT @s1 = @s2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (latin2_general_ci,IMPLICIT) for operation '='

将一个字符串转换为与另一个字符串兼容的字符集，这样比较就不会出现错误：

mysql> SELECT @s1 = CONVERT(@s2 USING latin1);
+---------------------------------+
| @s1 = CONVERT(@s2 USING latin1) |
+---------------------------------+
|                               1 |
+---------------------------------+

字符集转换在之前二进制字符串的字母大小写转换中也很有用。当直接应用于二进制字符串时，LOWER() 和 UPPER() 是无效的，因为字母大小写的概念不适用。要对二进制字符串进行字母大小写转换，首先使用与字符串中存储的数据相适应的字符集将其转换为非二进制字符串：

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+-------------+------------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
+-------------+------------------------------------+
| New York    | new york                           |
+-------------+------------------------------------+

注意，如果将 BINARY、CAST() 或 CONVERT() 应用到索引列，MySQL 可能无法有效地使用索引。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 转换函数和运算符(2)

评论