MySQL 数据类型转化

柔光的暖阳◎ 2021-06-24 16:10 496阅读 0赞

#### MySQL 提供的内置函数cast 和 convert 可以转换数据的类型 ####

**但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：**

二进制，同带binary前缀的效果 : BINARY
    字符型，可带参数 : CHAR()
    日期 : DATE
    时间: TIME
    日期时间型 : DATETIME
    浮点数 : DECIMAL
    整数 : SIGNED
    无符号整数 : UNSIGNED

##### 1、CAST(value as type) 就是CAST(xxx AS 类型) #####

字符串转整数:

mysql> select cast('sdf222222' as signed);           
    +-----------------------------+
    | cast('sdf222222' as signed) |
    +-----------------------------+
    |                           0 |
    +-----------------------------+
    1 row in set, 1 warning (0.00 sec)

浮点转字符串

mysql> select cast(23423423.2323 as char );      
    +------------------------------+
    | cast(23423423.2323 as char ) |
    +------------------------------+
    | 23423423.2323                |
    +------------------------------+
    1 row in set (0.00 sec)

浮点转字符串并且取别名

mysql> select cast(23423423.2323 as char ) as sss;
    +---------------+
    | sss           |
    +---------------+
    | 23423423.2323 |
    +---------------+
    1 row in set (0.00 sec)

##### 2、CONVERT(value, type) 就是CONVERT(xxx,类型) #####

浮点转字符串并且取别名

mysql> select convert(23423423.2323,char ) as sss;       
    +---------------+
    | sss           |
    +---------------+
    | 23423423.2323 |
    +---------------+
    1 row in set (0.00 sec)

浮点转整数

mysql> select convert(23423423.2222,signed ) ;
    +--------------------------------+
    | convert(23423423.2222,signed ) |
    +--------------------------------+
    |                       23423423 |
    +--------------------------------+
    1 row in set (0.00 sec)

字符串转整数

mysql> select convert('234sssss2',signed) ;         
    +-----------------------------+
    | convert('234sssss2',signed) |
    +-----------------------------+
    |                         234 |
    +-----------------------------+
    1 row in set, 1 warning (0.00 sec)