强制转换函数和运算符
表强制转换函数和运算符
名称 | 描述 |
---|---|
BINARY | 将字符串转换为二进制字符串 |
CAST() | 将值强制转换为特定类型 |
CONVERT() | 将值强制转换为特定类型 |
强制转换函数和运算符可将值从一种数据类型转换为另一种数据类型。
CONVERT()
一个USING
子句可在不同字符集之间转换数据:
CONVERT(exprUSING transcoding_name)
在MySQL中,转码名称与相应的字符集名称相同。
例子:
SELECT CONVERT('test'USING utf8mb4);SELECT CONVERT(_latin1'Müller'USING utf8mb4);INSERT INTO utf8mb4_table (utf8mb4_column)SELECT CONVERT(latin1_columnUSING utf8mb4)FROM latin1_table;
要在不同字符集之间转换字符串,也可以CONVERT()
不使用USING
或CAST()
:
CONVERT(string, CHAR[(N)]CHARACTER SET charset_name) CAST(stringAS CHAR[(N)]CHARACTER SET charset_name)
例子:
SELECT CONVERT('test', CHARCHARACTER SET utf8mb4);SELECT CAST('test'AS CHARCHARACTER SET utf8mb4);
如果您仅指定显示,则字符集和结果校验规则为,默认校验规则为。如果省略,则结果的字符集和校验规则由和变量定义,这些变量和系统变量确定默认的连接字符集和校验规则(请参见“连接字符集和校验规则”)。CHARACTER SET charset_name
charset_name
charset_name
CHARACTER SET charset_name
character_set_connection
collation_connection
或调用中COLLATE
不允许使用子句,但可以将其应用于函数结果。例如,这些是合法的:CONVERT()
CAST()
SELECT CONVERT('test'USING utf8mb4)COLLATE utf8mb4_bin;SELECT CONVERT('test', CHARCHARACTER SET utf8mb4)COLLATE utf8mb4_bin;SELECT CAST('test'AS CHARCHARACTER SET utf8mb4)COLLATE utf8mb4_bin;
但是这些是非法的:
SELECT CONVERT('test'USING utf8mb4COLLATE utf8mb4_bin);SELECT CONVERT('test', CHARCHARACTER SET utf8mb4COLLATE utf8mb4_bin);SELECT CAST('test'AS CHARCHARACTER SET utf8mb4COLLATE utf8mb4_bin);
通常,您不能以不BLOB
区分大小写的方式比较值或其他二进制字符串,因为二进制字符串使用binary
字符集,而该字符集与字母大小写概念无关。要执行不区分大小写的比较,请首先使用CONVERT()
或CAST()
函数将值转换为非二进制字符串。结果字符串的比较使用其校验规则。例如,如果转换结果字符集具有不区分大小写的校验规则,则LIKE
操作不区分大小写。对于以下操作,这是正确的,因为默认utf8mb4
校验规则(utf8mb4_0900_ai_ci
)不区分大小写:
SELECT 'A' LIKE CONVERT(blob_colUSING utf8mb4)FROM tbl_name;
要为转换后的字符串指定特定的校验规则,请COLLATE
在CONVERT()
调用之后使用一个子句:
SELECT 'A' LIKE CONVERT(blob_colUSING utf8mb4)COLLATE utf8mb4_unicode_ciFROM 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(@s2USING latin1); +--------------------------------- + | @s1 = CONVERT(@s2 USING latin1) | +--------------------------------- + | 1 | +--------------------------------- +
对于字符串文字,指定字符集的另一种方法是使用字符集介绍程序。_latin1
和_latin2
在前面的例子中是导引器的实例。与诸如CAST()
,或的CONVERT()
将字符串从一个字符集转换为另一字符集的转换函数不同,介绍程序将字符串文字指定为具有特定字符集,而无需进行转换。有关更多信息,请参见“字符集简介”。
字符集转换在二进制字符串的字母大写转换之前也很有用。LOWER()
并且UPPER()
直接应用于二进制字符串时无效,因为字母大小写的概念不适用。要执行二进制字符串的字母大小写转换,请首先使用适合于存储在字符串中的数据的字符集将其转换为非二进制字符串:
mysql>SET @str = BINARY 'New York'; mysql>SELECT LOWER(@str), LOWER(CONVERT(@strUSING utf8mb4)); +------------- +------------------------------------ + | LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) | +------------- +------------------------------------ + | New York | new york | +------------- +------------------------------------ +
请注意,如果使用BINARY
,CAST()
或转换索引列CONVERT()
,则MySQL可能无法有效使用索引。
强制转换功能对于在CREATE TABLE ... SELECT
语句中创建具有特定类型的列很有用:
mysql>CREATE TABLE new_tableSELECT 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=utf8mb4
强制转换功能对于按ENUM
词汇顺序对列进行排序很有用。通常,ENUM
使用内部数值对列进行排序。将值强制转换CHAR
为词法排序:
SELECT enum_colFROM tbl_nameORDER BY CAST(enum_colAS CHAR);
CAST()
如果将它用作更复杂的表达式(例如)的一部分,它也会更改结果CONCAT('Date:',CAST(NOW()AS DATE))
。
对于时间值,几乎不需要使用CAST()
提取不同格式的数据。相反,使用功能,例如EXTRACT()
,DATE_FORMAT()
或TIME_FORMAT()
。请参见“日期和时间函数”。
要将字符串转换为数字,通常只需要在数字上下文中使用字符串值即可:
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'
有关将数字隐式转换为字符串的信息,请参见“表达式求值中的类型转换”。
MySQL支持带符号和无符号64位值的算术运算。对于其中一个操作数是无符号整数的数字运算符(例如+
或-
),默认情况下结果是无符号的(请参见“算术运算符”)。要覆盖此内容,请使用SIGNED
或UNSIGNED
强制转换运算符分别将值强制转换为有符号或无符号64位整数。
mysql>SELECT 1 - 2; -> -1 mysql>SELECT CAST(1 - 2AS UNSIGNED ); -> 18446744073709551615 mysql>SELECT CAST(CAST(1 - 2AS UNSIGNED )AS SIGNED ); -> -1
如果任何一个操作数都是浮点值,则结果是浮点值,并且不受前面的规则的影响。(在这种情况下,DECIMAL
列值被视为浮点值。)
mysql>SELECT CAST(1AS UNSIGNED ) - 2.0; -> -1.0
SQL模式影响转换操作的结果(请参见“服务器SQL模式”)。例子:
- 用于将“零”日期字符串转换为日期,
CONVERT()
并在启用SQL模式时CAST()
返回NULL
并产生警告NO_ZERO_DATE
。 - 对于整数减法,如果
NO_UNSIGNED_SUBTRACTION
启用了 SQL模式,则即使任何操作数都为无符号,减法结果也将被签名。
以下列表描述了可用的强制转换函数和运算符:
BINARY
expr
BINARY
操作者将所表达的二进制串(即具有一个字符串binary
的字符集和binary
归类)。通常的用途BINARY
是强制使用数字字节值而不是一个字符一个字节地进行字符串比较。该BINARY
运营商还导致比较尾随空格是显著。有关之间的差异信息binary
的核对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
会影响整个操作;可以在两个操作数之前给出相同的结果。要将字符串表达式转换为二进制字符串,这些结构是等效的:
BINARY expr CAST(expr
AS BINARY) CONVERT(exprUSING BINARY)如果值是字符串文字,则可以使用
_binary
字符集介绍程序将其指定为二进制字符串,而无需执行任何转换:mysql>
SELECT 'a' = 'A'; -> 1 mysql>SELECT _binary 'a' = 'A'; -> 0有关介绍人的信息,请参见“字符串字符集和校验规则”。
BINARY
表达式中的运算符与BINARY
字符列定义中的属性在效果上有所不同。使用BINARY
属性定义的字符列分配有表默认字符集和该字符集的二进制(_bin
)归类。每个非二进制字符集都有一个_bin
校验规则。例如,如果表默认字符集为utf8mb4
,则这两个列定义是等效的:CHAR(10) BINARY CHAR(10)
CHARACTER SET utf8mb4COLLATE utf8mb4_bin使用
CHARACTER SET binary
中的定义CHAR
,VARCHAR
或TEXT
柱导致列与相应的二进制字符串的数据类型进行处理。例如,以下对定义是等效的:CHAR(10)
CHARACTER SET binary BINARY(10) VARCHAR(10)CHARACTER SET binary VARBINARY(10) TEXTCHARACTER SET binary BLOBCAST(expr AS type[ARRAY])
该
CAST()
函数采用任何类型的表达式,并产生指定类型的结果值,类似于CONVERT()
。有关更多信息,请参见CONVERT()
。在MySQL 8.0.17和以后,
InnoDB
允许使用一个附加的ARRAY
关键字,用于创建一个上的多值索引JSON
阵列的一部分CREATE INDEX
,CREATE TABLE
和ALTER TABLE
语句。ARRAY
不支持,除非用于在这些语句之一中创建多值索引时(在这种情况下是必需的)。被索引的列必须是类型的列JSON
。(CONVERT()
不不支持多值索引创建或ARRAY
关键字。)使用ARRAY
时,type
以下的AS
关键字可以指定任何所支持的类型的CAST()
,用的例外BINARY
和JSON
。有关语法信息和示例以及其他相关信息,请参见多值索引。CAST()
是标准的SQL语法。CONVERT(expr,type)
,CONVERT(expr USING transcoding_name)
该
CONVERT()
函数采用任何类型的表达式,并产生指定类型的结果值。语法的讨论也适用于,这是等效的。
CONVERT(expr,type)
CAST(expr AS type)
CONVERT(... USING ...)
是标准的SQL语法。的非USING
形式CONVERT()
是ODBC语法。CONVERT()
用USING
不同的字符集之间转换数据。在MySQL中,转码名称与相应的字符集名称相同。例如,以下语句将'abc'
默认字符集中的字符串转换为字符集中的相应字符串utf8mb4
:SELECT CONVERT('abc'USING utf8mb4);CONVERT()
不带,USING
并CAST()
使用一个表达式和一个type
值指定结果类型。这些type
值是允许的:BINARY[(N)]
产生具有
BINARY
数据类型的字符串。有关如何影响比较的描述,请参见“ 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
:产生具有给定字符集的字符串。ASCII
:的简写CHARACTER SET latin1
。UNICODE
:的简写CHARACTER SET ucs2
。
在所有情况下,字符串都具有字符集默认校验规则。
DATE
产生一个
DATE
值。DATETIME
产生一个
DATETIME
值。DECIMAL[(M[,D])]
产生一个
DECIMAL
值。如果给出了可选值M
和D
值,则它们指定最大位数(精度)和小数点后的位数(小数位数)。DOUBLE
产生
DOUBLE
结果。在MySQL 8.0.17中添加。FLOAT[(p)]
如果
p
未指定精度,则产生type的结果FLOAT
。如果p
提供并且0 <=<<p
= 24,则结果为类型FLOAT
。如果25 <=p
<= 53,则结果为类型DOUBLE
。如果p
<0或p
> 53,则返回错误。在MySQL 8.0.17中添加。JSON
产生一个
JSON
值。有关在JSON
和其他类型之间进行值转换的规则的详细信息,请参见 JSON值的比较和排序。NCHAR[(N)]
与相似
CHAR
,但会产生带有国家字符集的字符串。请参见“国家字符集”。与不同
CHAR
,NCHAR
不允许指定尾随字符集信息。REAL
产生type的结果
REAL
。这实际上是FLOAT
,如果REAL_AS_FLOAT
启用了SQL模式;否则结果为类型DOUBLE
。SIGNED[INTEGER]
产生一个有符号的整数值。
TIME
产生一个
TIME
值。UNSIGNED[INTEGER]
产生一个无符号整数值。