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)