用 cast 或者 convert 函数。
整数
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)