Mysql转换为十进制给出99.9

Question

我有一个 varchar 字段说值是 174 但是当我 cast(field as decimal(3,1)) 它总是给我 99.9？我怎样才能将它转换或转换为 17.4？或者我必须在源级别更改输入以十进制本身输入吗？

Answer 1

那是因为174不能用小数点前2位和小数点后1位来表示。 decimal(3,1) 表示“总共 3 位数字，小数点后有 1 位(共 3 位)。

mysql> select cast('174' as decimal(3,1));
+-----------------------------+
| cast('174' as decimal(3,1)) |
+-----------------------------+
|                        99.9 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

它给出了可以显示的警告:

mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast('174' as decimal(3,1))' at row 1 |
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

将数字除以 10，然后重试:

mysql> select cast('174'/10 as decimal(3,1));
+--------------------------------+
| cast('174'/10 as decimal(3,1)) |
+--------------------------------+
|                           17.4 |
+--------------------------------+
1 row in set (0.00 sec)