MySQL数据类型转化

MySQL提供的内置函数CAST和CONVERT可以转换数据的类型

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

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

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);      
+------------------------------------+
| SELECT 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)

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)