12.10 转换函数和运算符

table12.14 强制转换函数和运算符

NameDescription
BINARY将字符串转换为二进制字符串
CAST()将值强制转换为特定类型
CONVERT()将值强制转换为特定类型

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

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

CONVERT(expr USING transcoding_name)

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

Examples:

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)语法(不带USING)或CAST(expr AS 类型)(等效):

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

Examples:

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

如果仅显示CHARACTER SET charset_name,则结果的字符集和排序规则为* charset_name ,默认排序规则为 charset_name *。如果省略CHARACTER SET charset_name,则结果的字符集和排序规则由character_set_connectioncollation_connection系统变量定义,这些变量确定默认的连接字符集和排序规则(请参见第 10.4 节“连接字符集和排序规则”)。

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

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);

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

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

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

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

若要使用其他字符集,请在前面的语句中用其名称替换latin1(并类似地使用其他排序规则)。

CONVERT()CAST()通常可以用于比较以不同字符集 table 示的字符串。例如,对这些字符串进行比较会导致错误,因为它们具有不同的字符集:

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 |
+---------------------------------+

对于字符串 Literals,指定字符集的另一种方法是使用字符集介绍程序。上一个示例中的_latin1_latin2是介绍者的实例。与诸如CAST()CONVERT()之类的将字符串从一个字符集转换为另一字符集的转换函数不同,介绍程序将字符串 Literals 指定为具有特定字符集,而无需进行转换。有关更多信息,请参见第 10.3.8 节“字符集介绍者”

字符集转换在二进制字符串的字母大写转换之前也很有用。直接应用于二进制字符串时,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 可能无法有效使用索引。

强制转换功能对于在创建 table...选择语句中创建具有特定类型的列很有用:

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

强制转换功能对于按词法 Sequences 对ENUM列进行排序很有用。通常,使用内部数值对ENUM列进行排序。将值转换为CHAR会导致词法排序:

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

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

对于时间值,几乎不需要使用CAST()来提取不同格式的数据。而是使用EXTRACT()DATE_FORMAT()TIME_FORMAT()之类的函数。参见第 12.6 节“日期和时间函数”

要将字符串转换为数字,通常只需要在数字上下文中使用字符串值即可:

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

十六进制和位 Literals 也是如此,默认情况下是二进制字符串:

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

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

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

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

有关将数字隐式转换为字符串的信息,请参见第 12.2 节“table 达式评估中的类型转换”

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

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()CAST()返回NULL并在启用NO_ZERO_DATE SQL 模式时产生警告。

  • 对于整数减法,如果启用了NO_UNSIGNED_SUBTRACTION SQL 模式,则即使任何操作数都为无符号,减法结果也将被签名。

以下列 table 描述了可用的强制转换函数和运算符:

BINARY expr

BINARY运算符将 table 达式转换为二进制字符串(具有binary字符集和binary归类的字符串)。 BINARY的常见用法是强制使用数字字节值而不是一个字符一个字节地进行字符串比较。 BINARY运算符还会使比较中的尾随空格显着。有关binary字符集的binary归类和非二进制字符集的_bin归类之间的差异的信息,请参见第 10.8.5 节“与_bin 归类相比的二进制归类”

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

相比之下,BINARY影响整个操作;可以在两个操作数之前给出相同的结果。

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

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

如果值是字符串 Literals,则可以使用_binary字符集介绍程序将其指定为二进制字符串,而无需执行任何转换:

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

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

table 达式中的BINARY运算符与字符列定义中的BINARY属性的作用不同。使用BINARY属性定义的字符列分配了 table 默认字符集和该字符集的二进制(_bin)归类。每个非二进制字符集都有一个_bin归类。例如,如果 table 默认字符集是utf8,则这两个列定义是等效的:

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

CHARVARCHARTEXT列的定义中使用CHARACTER SET binary会将列视为相应的二进制字符串数据类型。例如,以下对定义是等效的:

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

CAST(expr AS 类型)

CAST()函数采用任何类型的 table 达式,并产生指定类型的结果值,类似于CONVERT()。有关更多信息,请参见CONVERT()的描述。

CONVERT(使用 transcoding_name 的 table 达式), CONVERT(expr,type)

CONVERT()函数采用任何类型的 table 达式,并产生指定类型的结果值。

转换(...正在使用...)是标准 SQL 语法。 CONVERT()的非USING形式是 ODBC 语法。

CONVERT(使用 transcoding_name 的 table 达式)在不同字符集之间转换数据。在 MySQL 中,转码名称与相应的字符集名称相同。例如,以下语句将默认字符集中的字符串'abc'转换为utf8字符集中的相应字符串:

SELECT CONVERT('abc' USING utf8);

CONVERT(expr, type)语法(不带USING)采用一个 table 达式和一个* type 值来指定结果类型。此操作也可以 table 示为CAST(expr AS 类型),等效。这些 type *值是允许的:

  • BINARY[(N)]

产生一个具有BINARY数据类型的字符串。有关此操作如何影响比较的说明,请参见第 11.3.3 节“ BINARY 和 VARBINARY 类型”。如果给出了可选的长度* N ,则BINARY(N)导致强制类型转换使用的参数字节数不超过 N 字节。小于 N 字节的值用0x00字节填充,长度为 N *。

  • CHAR[(N)] [charset_info]

产生一个具有CHAR数据类型的字符串。如果给出了可选的长度* N ,则CHAR(N)导致强制类型转换使用不超过参数的 N 个字符。小于 N *字符的值不会发生填充。

如果没有* charset_info 子句,则CHAR会产生具有默认字符集的字符串。为了明确指定字符集,允许使用这些 charset_info *值:

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

    • ASCIICHARACTER SET latin1的简写。

    • UNICODECHARACTER SET ucs2的简写。

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

  • DATE

产生一个DATE值。

  • DATETIME

产生一个DATETIME值。

  • DECIMAL[(M[,D])]

产生一个DECIMAL值。如果给出了可选的* M D *值,它们将指定最大位数(精度)和小数点后的位数(小数位数)。

  • JSON

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

  • NCHAR[(N)]

类似于CHAR,但是会产生一个带有国家字符集的字符串。参见第 10.3.7 节“国家字符集”

CHAR不同,NCHAR不允许指定结尾字符集信息。

  • SIGNED [INTEGER]

产生一个有符号的整数值。

  • TIME

产生一个TIME值。

  • UNSIGNED [INTEGER]

产生一个无符号整数值。