table 12.14 转换函数(cast function)
名称 说明
binary 将 string 转换为二进制 string
cast() 将某个值转换为特定类型
convert() 将某个值转换为特定类型
binarybinary 运算符将紧随其后的 string 转换为 二进制字符串。主要用来强制进行按字节进行比较(byte by byte),字节而不是字符的字符。这使得字符串比较是区分大小写的, 不管原始的列定义是否是 binary 或者 blob。binary 也对字符串末尾的空格敏感。
select 'a' = 'a';
1
select binary 'a' = 'a';
0
select 'a' = 'a ';
1
select binary 'a' = 'a ';
0
在上面的比较中, binary 影响的是整个比较操作; 不管哪个操作数放在前面, 结果都是一样的。
还有一种情况，binary 不对等号起作用：
select 'a' = binary 'a ';
1
binary str 其实是 cast(str as binary) 的缩写。
有时候, 如果将索引列转换为 binary, mysql可能不会使用索引。
cast(expr as type)cast() 函数接收任意类型的表达式, 并根据指定类型返回相应的结果值, 跟 convert() 很相似, 除了使用的语法形式上有一点区别, 所以请参考下面的 convert() 函数。
convert(expr,type), convert(expr using transcoding_name)convert()和 cast() 函数都是接收任意类型的表达式, 并根据指定类型返回相应的结果值。
cast() 和 convert(... using ...) 都是标准的sql语法。而没有 using 的 convert() 是 odbc 的语法。
using 方式的 convert() 在不同的字符集之间进行数据转换。在mysql中, 转码的名称和相应的字符集名称一致。例如, 下面的语句将字符串 ‘abc’ 从默认字符集转换为 utf8 字符集:
select convert('abc' using utf8);
转换函数的结果可以是以下这些类型:
binary[(n)]
char[(n)]
date
datetime
decimal[(m[,d])]
signed [integer]
time
unsigned [integer]
binary 生成的是二进制形式的 string 数据类型。更多细节请参考 11.4.2节, “the binary and varbinary types” 。如果传入了可选参数n, 那么 binary(n) 的转换结果最多为 n 个字节。如果结果小于n个字节,则用 0x00 来填充。
char(n)的结果为最多n个字符。
一般来说,用不区分大小写的方式并不能比较 blob 值或者其他二进制串, 因为二进制串是没有字符集的,因此也没有字母的概念。如果要不区分大小写, 可以用 convert() 将值转换为非二进制的字符串再来比较。比较的结果根据字符集排序而定。例如,假设字符集不区分大小写, 那么 like 操作也就不区分大小写:
select 'a' like convert(blob_col using latin1) from tbl_name;
要使用其他字符集, 只要把里面的 latin1 替换掉就行。为转换后的字符串指定特定的排序规则, 可以在 convert() 函数调用后面跟上 collate 从句, 正如 10.1.9.2 节 “convert() and cast()” 中所描述的. 例如,使用 latin1_german1_ci 排序:
select 'a' like convert(blob_col using latin1) collate latin1_german1_ci from tbl_name;
convert() 可以在不同的字符集之中进行比较。
lower() 和 upper() 对于二进制字符串是无效的(包括 binary, varbinary, blob)。要进行大小写转换,需要先将字符串转换成非二进制形式:
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 |+-------------+-----------------------------------+
转换函数可以用来创建特定类型的列,比如在 create table ... select语句之中:
create table new_table select cast('2000-01-01' as date);
转换函数也可以用来按定义的单词将 enum 列排序 。正常情况下, 枚举列是根据内部的数值表示来进行排序的。按字母排序 char 类型的结果:
select enum_col from tbl_name order by cast(enum_col as char);
cast(str as binary) 和 binary str 等价。cast(expr as char) 将表达式当作默认字符集来处理。
cast() 可能会改变复杂表达式的结果，例如 concat('date: ',cast(now() as date))。
这里就不应该使用 cast() 来提取不同格式的数据,而应该使用字符串函数，如 left() 或者 extract()。详情请参考 section 12.7, “date and time functions”。
要把字符串转换为数值来进行处理, 一般是不需要手工处理的，mysql会进行隐式的类型转换:
select 1+'1';
2
在算术运算中, string 会在表达式求值阶段转换为浮点数。
如果需要将数字当成字符串来处理, mysql也会自动进行转换:
select concat('hello you ',2);
‘hello you 2’
在 mysql 5.6.4之前的版本,用 cast() 处理 timestamp 时， 如果不从具体的表中选取值, mysql 5.6 会在执行转换之前把值优先当成字符串来对待。这在转换为数字时可能会导致截断,如下所示:
mysql> select cast(timestamp '2014-09-08 18:07:54' as signed);+-------------------------------------------------+| cast(timestamp '2014-09-08 18:07:54' as signed) |+-------------------------------------------------+| 2014 |+-------------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+---------+------+----------------------------------------------------------+| level | code | message |+---------+------+----------------------------------------------------------+| warning | 1292 | truncated incorrect integer value: '2014-09-08 18:07:54' |+---------+------+----------------------------------------------------------+1 row in set (0.00 sec)
但如果从一张表中选取行时并不会这样,如下所示:
use test;
database changed
create table c_test (col timestamp);
query ok, 0 rows affected (0.07 sec)
insert into c_test values ('2014-09-08 18:07:54');
query ok, 1 row affected (0.05 sec)
select col, cast(col as unsigned) as c_col from c_test;
>
+———————+—————-+
| col | c_col |
+———————+—————-+
| 2014-09-08 18:07:54 | 20140908180754 |
+———————+—————-+
1 row in set (0.00 sec)
在mysql 5.6.4 之后, 修复了这个问题,如下所示:
select cast(timestamp '2014-09-08 18:07:54' as signed);
>
+————————————————-+
| cast(timestamp ‘2014-09-08 18:05:07’ as signed) |
+————————————————-+
| 20140908180754 |
+————————————————-+
1 row in set (0.00 sec)
关于数字和字符串的隐式转换, 参见 12.2节 “type conversion in expression evaluation”.
mysql支持有符号的和无符号的64位算术运算。如果您使用的是数字运算符(如加 + 或减 -), 其中的一个操作数是无符号整数, 那默认情况下结果就是无符号数(参见 12.6.1 算术运算符)。可以通过指定 signed 或者 unsigned 来进行转换。
select cast(1-2 as unsigned)
18446744073709551615
select cast(cast(1-2 as unsigned) as signed);
-1
如果有操作数是浮点值, 那么结果就是浮点值, 不受前面规则的影响。(在这种情况下, decimal 列被视为浮点值。)
select cast(1 as unsigned) - 2.0;
-1.0
sql模式影响转换操作的结果。例如:
如果转换零值的日期串为日期, convert() 和 cast() 都会返回 null , 并在 no_zero_date 模式下产生警告。
对于整数的减法,如果启用了 no_unsigned_subtraction 模式, 减法结果是有符号数,即便其中一个是无符号数。
更多信息请参见 5.1.7节 “server sql modes”。
如何将 blob 转换为 utf8 的 char 首先，请查看 blob 里面存储的是什么编码的byte。是 utf8 还是其他字符集?
cast(a.ar_options as char(10000) character set utf8)
在这里必须指定正确的字符集, 对应于 blob 中存储的编码。如果里面存储的是 utf8编码, 那么就是上面这样。如果存储的是 latin1 字符集, 那么就需要设置为 latin1 。