扩展 MySQL 8.0  / 第 6 章 向 MySQL 添加函数  /  12.11 转换函数和运算符

12.11 转换函数和运算符

表 12.15 转换函数和运算符

姓名 描述
BINARY 将字符串转换为二进制字符串
CAST() 将值转换为特定类型
CONVERT() 将值转换为特定类型

转换函数和运算符可以将值从一种数据类型转换为另一种数据类型。

Cast 函数和运算符说明

  • BINARY expr

    运算符将BINARY表达式转换为二进制字符串(具有 binary字符集和 binary排序规则的字符串)。for 的一个常见用途 BINARY是强制使用数字字节值而不是逐个字符地逐字节进行字符串比较。BINARY运算符还会导致比较中的尾随空格很重要。 有关字符集排序规则与非二进制字符集排序规则之间差异的信息 , binary请 参阅第 10.8.5 节,“二进制排序规则与 _bin 排序规则的比较”binary_bin

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT BINARY 'a' = 'A';
            -> 0
    mysql> SELECT 'a' = 'a ';
            -> 1
    mysql> SELECT BINARY 'a' = 'a ';
            -> 0

    在比较中,BINARY影响整个操作;它可以在具有相同结果的任一操作数之前给出。

    要将字符串表达式转换为二进制字符串,这些结构是等效的:

    CONVERT(expr USING BINARY)
    CAST(expr AS BINARY)
    BINARY expr

    如果一个值是一个字符串字面量,它可以指定为一个二进制字符串而不用使用 _binary字符集引入器转换它:

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT _binary 'a' = 'A';
            -> 0

    有关介绍人的信息,请参阅 第 10.3.8 节,“字符集介绍人”

    表达式中的BINARY运算符在效果上不同于 BINARY字符列定义中的属性。对于用 BINARY属性定义的字符列,MySQL 分配表默认字符集和该字符集的二进制 ( _bin) 排序规则。每个非二进制字符集都有一个_bin 排序规则。例如,如果表默认字符集是utf8,则这两个列定义是等效的:

    CHAR(10) BINARY
    CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin

    在、 或 列CHARACTER SET binary的定义中 使用会导致该列被视为相应的二进制字符串数据类型。例如,下面的定义对是等价的: CHARVARCHARTEXT

    CHAR(10) CHARACTER SET binary
    BINARY(10)
    
    VARCHAR(10) CHARACTER SET binary
    VARBINARY(10)
    
    TEXT CHARACTER SET binary
    BLOB

    如果BINARYmysql客户端中调用,则二进制字符串使用十六进制表示法显示,具体取决于--binary-as-hex. 有关该选项的更多信息,请参阅 第 4.5.1 节,“mysql — MySQL 命令行客户端”

  • CAST(expr AS type)

    CAST(expr AS type采用任何类型的表达式并生成指定类型的结果值。这个操作也可以表示为 ,是等价的。 CONVERT(expr, type)

    这些type值是允许的:

    • BINARY[(N)]

      生成 VARBINARY数据类型为 string 的字符串,但当表达式 expr为空(零长度)时,结果类型为BINARY(0). 如果给出了可选长度N,则 导致转换使用不超过 参数的字节数。短于字节的值用字节填充到长度为. 如果可选长度BINARY(N)NN0x00NN没有给出,MySQL 计算表达式的最大长度。如果提供或计算的长度大于内部阈值,则结果类型为 BLOB. 如果长度仍然太长,则结果类型为LONGBLOB.

      有关转换为如何 BINARY影响比较的描述,请参阅 第 11.3.3 节,“BINARY 和 VARBINARY 类型”

    • CHAR[(N)] [charset_info]

      生成具有 VARCHAR数据类型的字符串。除了当表达式expr 为空(零长度)时,结果类型为 CHAR(0). 如果给出了可选长度 N,则 导致强制转换使用不超过 参数的字符。对于比字符短的值,不会进行填充 。如果未给出可选长度,MySQL 将根据表达式计算最大长度。如果提供或计算的长度大于内部阈值,则结果类型为 CHAR(N)NNNTEXT. 如果长度仍然太长,则结果类型为LONGTEXT.

      如果没有charset_info子句, CHAR则生成具有默认字符集的字符串。要明确指定字符集, charset_info允许使用以下值:

      • CHARACTER SET charset_name:生成具有给定字符集的字符串。

      • ASCII: 的简写 CHARACTER SET latin1

      • UNICODE: 的简写 CHARACTER SET ucs2

      在所有情况下,字符串都具有字符集默认排序规则。

    • DATE

      产生一个DATE值。

    • DATETIME[(M)]

      产生一个DATETIME 值。如果给出可选M 值,则它指定小数秒精度。

    • DECIMAL[(M[,D])]

      产生一个DECIMAL值。如果给出可选的MD值,它们指定最大位数(精度)和小数点后的位数(比例)。如果D省略,则假定为 0。如果M省略,则假定为 10。

    • JSON

      产生一个JSON值。有关值JSON与其他类型之间的值转换规则的详细信息,请参阅JSON 值的比较和排序

    • NCHAR[(N)]

      与 类似CHAR,但会生成具有国家字符集的字符串。请参阅 第 10.3.7 节,“国家字符集”

      与 不同CHARNCHAR 不允许指定尾随字符集信息。

    • SIGNED [INTEGER]

      产生一个带符号的BIGINT 值。

    • TIME[(M)]

      产生一个TIME值。如果给出可选M值,则它指定小数秒精度。

    • UNSIGNED [INTEGER]

      产生一个无符号 BIGINT值。

  • CONVERT(expr USING transcoding_name)

    CONVERT(expr,type)

    CONVERT(expr USING transcoding_name) 是标准的 SQL 语法。的非USING 形式CONVERT()是 ODBC 语法。

    CONVERT(expr USING transcoding_name) 在不同字符集之间转换数据。在MySQL中,转码名称与对应的字符集名称相同。例如,此语句将'abc'默认字符集中的字符串转换为字符集中对应的字符串 utf8

    SELECT CONVERT('abc' USING utf8);

    CONVERT(expr, type)syntax (without USING) 采用表达式和 type指定结果类型的值,并生成指定类型的结果值。这个操作也可以表示为 ,是等价的。有关详细信息,请参阅 的说明 。 CAST(expr AS type)CAST()

字符集转换

CONVERT()with USING子句在字符集之间转换数据:

CONVERT(expr USING transcoding_name)

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

例子:

SELECT CONVERT('test' USING utf8);
SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8_table (utf8_column)
    SELECT CONVERT(latin1_column USING utf8) FROM latin1_table;

要在字符集之间转换字符串,您还可以使用 语法(不带 )或 ,它们是等效的: CONVERT(expr, type)USINGCAST(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 utf8);
SELECT CAST('test' AS CHAR CHARACTER SET utf8);

如果您像刚才显示的那样指定,则结果的字符集和排序规则是 默认排序规则。如果省略,则结果的字符集和排序 规则由确定默认连接字符集和排序规则的和 系统变量定义(请参阅第 10.4 节,“连接字符集和排序规则”)。 CHARACTER SET charset_namecharset_namecharset_nameCHARACTER SET charset_namecharacter_set_connectioncollation_connection

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

SELECT CONVERT('test' USING utf8) COLLATE utf8_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8) COLLATE utf8_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

但这些是非法的:

SELECT CONVERT('test' USING utf8 COLLATE utf8_bin);
SELECT CONVERT('test', CHAR CHARACTER SET utf8 COLLATE utf8_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8 COLLATE utf8_bin);

对于字符串文字,另一种指定字符集的方法是使用字符集介绍器。在前面的示例中是介绍人的实例_latin1_latin2CAST(), 或 CONVERT()等将字符串从一种字符集转换为另一种字符集的转换函数不同,引入符将字符串文字指定为具有特定字符集,不涉及转换。有关详细信息,请参阅 第 10.3.8 节,“字符集介绍人”

字符串比较的字符集转换

通常,您不能以 BLOB不区分大小写的方式比较值或其他二进制字符串,因为二进制字符串使用的 binary字符集与字母大小写的概念没有排序规则。要执行不区分大小写的比较,首先使用 CONVERT()or CAST()函数将值转换为非二进制字符串。结果字符串的比较使用它的排序规则。例如,如果转换结果排序规则不区分大小写, LIKE则操作不区分大小写。对于以下操作也是如此,因为默认latin1排序规则 ( latin1_swedish_ci) 不区分大小写:

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

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

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;

要使用不同的字符集,请将其名称替换为 latin1前面的语句(并且类似地使用不同的归类)。

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 latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |
+-------------+-----------------------------------+

请注意,如果您将BINARYCAST()CONVERT()应用于索引列,MySQL 可能无法有效地使用索引。

Cast 操作的其他用途

CREATE TABLE ... SELECT强制转换函数对于在语句 中创建具有特定类型的列很有用 :

mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;
mysql> SHOW CREATE TABLE new_table\G
*************************** 1. row ***************************
       Table: new_table
Create Table: CREATE TABLE `new_table` (
  `c1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

强制转换函数对于按 ENUM词法顺序对列进行排序很有用。ENUM通常,使用内部数值对列进行排序。将值转换 CHAR为词法排序结果:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST()如果将结果用作更复杂的表达式(例如 .)的一部分,也会更改结果 CONCAT('Date: ',CAST(NOW() AS DATE))

对于时间值,几乎不需要使用 CAST()不同格式的数据来提取。相反,请使用诸如 EXTRACT()DATE_FORMAT()或 之类的函数TIME_FORMAT()。请参阅 第 12.7 节,“日期和时间函数”

要将字符串转换为数字,通常在数字上下文中使用字符串值就足够了:

mysql> SELECT 1+'1';
       -> 2

对于默认情况下为二进制字符串的十六进制和位文字也是如此:

mysql> SELECT X'41', X'41'+0;
        -> 'A', 65
mysql> SELECT b'1100001', b'1100001'+0;
        -> 'a', 97

算术运算中使用的字符串在表达式求值期间转换为浮点数。

字符串上下文中使用的数字转换为字符串:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

有关将数字隐式转换为字符串的信息,请参阅第 12.3 节,“表达式求值中的类型转换”

MySQL 支持带符号和无符号 64 位值的算术。对于其中一个操作数是无符号整数的数字运算符(例如 +or ),默认情况下结果是无符号的(请参阅第 12.6.1 节,“算术运算符”)。要覆盖它,请使用or cast 运算符分别将值转换为有符号或无符号的 64 位整数。 -SIGNEDUNSIGNED

mysql> SELECT 1 - 2;
        -> -1
mysql> SELECT CAST(1 - 2 AS UNSIGNED);
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);
        -> -1

如果任一操作数是浮点值,则结果是浮点值并且不受前面规则的影响。(在此上下文中,DECIMAL列值被视为浮点值。)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

SQL 模式会影响转换操作的结果(请参阅 第 5.1.10 节,“服务器 SQL 模式”)。例子:

  • 用于将日期字符串转换为日期,CONVERT()并 在启用 SQL 模式 时CAST()返回 NULL并产生警告 。NO_ZERO_DATE

  • 对于整数减法,如果 NO_UNSIGNED_SUBTRACTION 启用了SQL模式,即使任何操作数是无符号的,减法结果也是有符号的。