Mysql类型转换的简单了解

来源：互联网发布：php程序设计读后感编辑：程序博客网时间：2024/06/06 21:04

由于工作需要，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操作也不会区分大小写：

SELECT 'A' LIKE CONVERT( value USING latin1 ) FROM table1;

②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

无符号数，先取整后计算。结果为负时会报错。

0 0