mysql数据类型转换

MySQL 提供的内置函数cast 和 convert 可以转换数据的类型
但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：

1.二进制，同带binary前缀的效果 : BINARY
2.字符型，可带参数 : CHAR()
3.日期 : DATE
4.时间: TIME
5.日期时间型 : DATETIME
6.浮点数 : DECIMAL
7.整数 : SIGNED
8.无符号整数 : 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)