如何从MySQL中选择一个浮点数时禁用指数表示法？ [英] how can I disable exponential notation when selecting a float from MySQL?

问题描述

我有一个FLOAT列，其中包含一些非常小的数字，例如0.00000000000234

当我在MySQL中选择它们时，我将它们以指数表示法返回，如2.34e-13 。有没有什么办法可以修改这些数字的格式来强制MySQL将它返回为0.00000000000234？

最终我要做的是修改显示，而不是数字的底层表示。所以这可能是一个显示或格式设置。 MySQL有一个FORMAT（）函数，但它不是我想要的（它将数字的格式设置为123,456.78）。

解决方案

您可以将数字强制回到所需的显示格式。将其转换为足够大的DECIMAL值，然后将DECIMAL强制转换为CHAR，以便获得之后的显示形式。这是纯粹的显示，并允许你继续存储浮动在MySQL（如果需要的话）。
$ b $ pre $ mysql> SELECT CAST（CAST（f AS DECIMAL（14,14））AS CHAR）作为例子从测试;
+ ------------------ +
|例子|
+ ------------------ +
| 0.00000000000234 |
+ ------------------ +
1行（0.00秒）


您将失去一些精确度，当然取决于数值。

I have a FLOAT column that contains some very small numbers like 0.00000000000234

When I select them in MySQL, I get them back in exponential notation like 2.34e-13. Is there any way I can modify the formatting of these numbers to force MySQL to return it as 0.00000000000234 ?

Ultimately what I'd be looking to do is modify the display, not the underlying representation of the number. So it would probably be a display or formatting setting. MySQL has a FORMAT() function, but it's nothing like what I want (it puts numbers in format like 123,456.78).

解决方案

You can coerce the number back to the desired display format. Cast it to a large enough DECIMAL value and then cast the DECIMAL to a CHAR in order to get the display form you are after. This is purely display and allows you to continue to store floats in mysql (if so desired).

mysql> SELECT CAST(CAST(f AS DECIMAL(14,14)) AS CHAR) AS example from test;
+------------------+
| example          |
+------------------+
| 0.00000000000234 |
+------------------+
1 row in set (0.00 sec)

You will lose some precision of course, depending on the value.

这篇关于如何从MySQL中选择一个浮点数时禁用指数表示法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！