由于工作需要,Mysql在使用时需要做一些强制的类型转换,在网上查阅了一些大神的分享,就做了下简单整理。水平不高,可能会有些问题不准确。
-------------- ---------------- ---------------- -------------- ---------------- ---------------- -------------- ---------------- ---------------- -------------- ---------------- ----------------
一、两种类型转换方式:
1.CAST ( value as type );
2.CONVERT ( value , type );
3.CONVERT( expr USING transcoding_name);
4.CONCAT
5.if函数
6.SELECT conv('A09C012CA92A',16,10) 将16进制转换为10进制
CAST() and CONVERT(... USING ...) 是标准 SQL语法。而CONVERT()的非USING 格式是ofis ODBC语法。
带有USING的CONVERT() 被用来在不同的字符集之间转化数据。在 MySQL中, 自动译码名和相应的字符集名称相同。例如。 这个语句将服务器的默认字符集中的字符串 'abc'转化为utf-8字符集中相应的字符串:
SELECT CONVERT('abc' USING utf8); (不区分大小写,但是不识别utf-8)
当你想要在一个CREATE ... SELECT 语句中创建一个特殊类型的列,则cast函数会很有用:
CREATE TABLE new_table SELECT CAST('2017-04-12' AS DATE);
concat函数,常用于连接字符串。如sql查询条件的like查询,
AND c.name like concat(#{param.value},'%')。
将Int 转为varchar经常用 concat函数,比如concat(1,'9') 得到字符串 '19',但是可能会有隐式类型转化的问题。
IF函数 mysql中if是函数而不是命令 IF(expr1,expr2,expr3)
如果 expr1 为真(expr1 <> 0 以及 expr1 <> NULL),那么 IF() 返回 expr2,否则返回 expr3。
IF() 返回一个数字或字符串,这取决于它被使用的语境:
mysql> SELECT IF(1>2,2,3); -> 3
mysql> SELECT IF(1<2,'yes','no'); -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'
如果 expr2 或 expr3 明确地为 NULL,那么函数 IF() 的返回值类型为非 NULL 列的类型。(这在 MySQL 4.0.3 中新加入)。 expr1 是作为一个整数值被计算的,这就意味着,如果测试的是一个浮点型或字符串值,就必须进行比较操作:
mysql> SELECT IF(0.1,1,0); -> 0
mysql> SELECT IF(0.1<>0,1,0); -> 1
在上面第一种情况下,IF(0.1) 返回 0,是因为 0.1 被转换为一个整数值,返回 IF(0) 的测试结果。这可能不是你所期望的。在第二种情况下,比较测试原浮点数是否为一个非零值。比较的结果被作为整数使用。 缺省的 IF() 返回值类型 (当结果存储在临时表中时,这是非常重要的) 在 MySQL 3.23 中按下列方式确定: 表达式 返回值
表达式(expr2)或表达式(expr3)返回值为字符串 字符串
表达式(expr2)或表达式(expr3)返回值为浮点型值 浮点型
表达式(expr2)或表达式(expr3)返回值为整型 整型
如果表达式(expr2)和表达式(expr3)均是字符串,同时两个字符串均是忽略字母大小写的,那么返回值也是忽略字母大小写的(从 MySQL 3.23.51 开始)。
二、可转换的类型:
①BINARY
BINARY str 是CAST(str AS BINARY)的缩略形式。
BINARY不是函数,是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解为在字符串比较的时候区分大小写。
因为有的MySQL特别是4.*以前的对于中文检索会有不准确的问题,可以在检索的时候加上BINARY。
BINARY保存二进制字符串,它保存的是字节而不是字符,没有字符集限制。(例如:binary(8)可以保存8个字符,每个字符占1个字节,共占8个字节。)进行比较时是按字节进行比较,而不是按字符(char),按字节比较比字符简单快速。按字符比较不区分大小写,而binary区分大小写,结尾使用\0填充,而不是空格。
假如给定了随意长度N,则 BINARY[N] 使 cast使用该参数的不多于 N 个字节。同样的, CHAR[N]会使 cast 使用该参数的不多于N 个字符。
在一些语境中,假如你将一个编入索引的列派给BINARY, MySQL将不能有效使用这个索引。为执行一个区分大小写的比较,可使用CONVERT()函数将一个字符串值转化为一个不区分大小写的字符集。其结果为一个非二进制字符串,因此LIKE操作也不会区分大小写:
②CHAR
· char使用固定长度的空间进行存储,char(4)存储4个字符,根据编码方式的不同占用不同的字节,gbk编码方式,不论是中文还是英文,每个字符占用2个字节的空间,utf8编码方式,每个字符占用3个字节的空间。
如果需要存储的字符串的长度跟所有值的平均长度相差不大,适合用char,如MD5。对于经常改变值,char优于varchar,原因是固定长度的行不容易产生碎片。对于很短的列,char优于varchar,原因是varchar需要额外一个或两个字节存储字符串的长度。
· varchar保存可变长度的字符串,使用额外的一个或两个字节存储字符串长度,varchar(10),除了需要存储10个字符,还需要1个字节存储长度信息(10),超过255的长度需要2个字节来存储
例外:Myisam(MySql默认)引擎中使用ROW_FORMAT=FIXED(静态表)时,每行使用相同的空间,造成浪费。
char和varchar后面如果有空格,char会自动去掉空格后存储,varchar虽然不会去掉空格,但在进行字符串比较时,会去掉空格进行比较。
③DATE
将数字以秒计算为时间。将字符串作为参数时,单位为毫秒 以下是实验结果
SELECT from_unixtime(1765915176) -->2025-12-17 03:59:36
SELECT from_unixtime(1765915176.00001) -->2025-12-17 03:59:36.00001
SELECT from_unixtime('1765915176') -->2025-12-17 03:59:36.000000
SELECT from_unixtime('1765915176.00001') -->2025-12-17 03:59:36.000010
但是有最高值限制 2038-01-19 11:14:08.000000, (2147483648-0.0000001)
最低为0或者null时:1970-01-01 08:00:00 默认的起始时间,数值都是自己用mysql计算出来的,不知道会不会有其他因素限制。
④TIME
随便试了一下 应该是最大的运行结果 并且对于小数部分不起作用
select convert('888992222.00001',time) -->838:59:59
select cast('888992233' as time) -->838:59:59
数值型的在运行时 分秒的位置需要<60,同样 小数部分没发现对结果有影响
⑤DATETIME
如果使用sql,MySql同时支持
select cast "2010-07-03 10:26:46" as date
select cast "2010-07-03 10:26:46" as datetime
在Hql中比较时间时,hibernate语句 不能使用第二个,因为hibernate不支持。
select convert('2017-05-12 22:22:22',datetime);
select cast('2017-05-12 22:22:22' as datetime);
都是必须把格式对应完整时才会生效,后面的时分秒如果不加,会自动设定为00:00:00
⑥DECIMAL
大量数据计算时可以保留精度。
⑦SIGNED
符号数整数,转为Integer时 Integer可以省略
select cast('456123.12345' as signed Integer)
⑧UNSIGNED
无符号数,先取整后计算。结果为负时会报错。