MySQL cast 函数

介绍

cast 函数用于转换变量类型。官方文档: https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html 。

as 后面的参数支持 char、signed、unsigned date、datetime、time、decimal 等。

支持转换字符集。

convert 函数与 cast 功能类似。

示例

mysql> select cast(1123 as char)
+--------------------+
| cast(1123 as char) |
+--------------------+
| 1123               |
+--------------------+


mysql> select cast("1123" as decimal)
+-------------------------+
| cast("1123" as decimal) |
+-------------------------+
| 1123                    |
+-------------------------+


mysql> select cast("1123" as decimal(10,2))
+-------------------------------+
| cast("1123" as decimal(10,2)) |
+-------------------------------+
| 1123.00                       |
+-------------------------------+

mysql> select cast('123' as signed)
+-----------------------+
| cast('123' as signed) |
+-----------------------+
| 123                   |
+-----------------------+

mysql> select cast('-123' as unsigned)
+--------------------------+
| cast('-123' as unsigned) |
+--------------------------+
| 18446744073709551493     |
+--------------------------+

mysql> select cast('-123.45' as signed)
+---------------------------+
| cast('-123.45' as signed) |
+---------------------------+
| -123                      |
+---------------------------+

字符串转整数示例

mysql> select cast('123' as unsigned);
+-----------------------+
| cast(123 as unsigned) |
+-----------------------+
|                   123 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select cast('123' as signed);
+---------------------+
| cast(123 as signed) |
+---------------------+
|                 123 |
+---------------------+
1 row in set (0.00 sec)


mysql> select cast('12300001111' as signed integer);
+-------------------------------------+
| cast(12300001111 as signed integer) |
+-------------------------------------+
|                         12300001111 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('123000011112222' as signed integer);
+-----------------------------------------+
| cast(123000011112222 as signed integer) |
+-----------------------------------------+
|                         123000011112222 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('-123000011112222' as signed integer);
+------------------------------------------+
| cast(-123000011112222 as signed integer) |
+------------------------------------------+
|                         -123000011112222 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('-123000011112222.123' as signed integer);
+----------------------------------------------+
| cast(-123000011112222.123 as signed integer) |
+----------------------------------------------+
|                             -123000011112222 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('-123000011112222.823' as signed integer);
+----------------------------------------------+
| cast(-123000011112222.823 as signed integer) |
+----------------------------------------------+
|                             -123000011112223 |
+----------------------------------------------+
1 row in set (0.00 sec)

字符串转double示例

mysql> select cast('-123002222.823' as double);
+----------------------------------+
| cast('-123002222.823' as double) |
+----------------------------------+
|                   -123002222.823 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select cast('-123000011112222.823' as double);
+----------------------------------------+
| cast('-123000011112222.823' as double) |
+----------------------------------------+
|                    -123000011112222.83 |
+----------------------------------------+
1 row in set (0.00 sec)

字符串转decimal示例

mysql> select cast('-123002222.823' as decimal);
+-----------------------------------+
| cast('-123002222.823' as decimal) |
+-----------------------------------+
|                        -123002223 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select cast('-123002222.823' as decimal(10, 2));
+------------------------------------------+
| cast('-123002222.823' as decimal(10, 2)) |
+------------------------------------------+
|                             -99999999.99 |    -- 这个结果有问题
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('-123002222.823' as decimal(20, 2));
+------------------------------------------+
| cast('-123002222.823' as decimal(20, 2)) |
+------------------------------------------+
|                            -123002222.82 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)