关闭科学记数法MySQL [英] Turn off scientific notation MySQL

问题描述

当插入DOUBLE时，为什么选择该值时会看到类似9.755046187483832e17的值?我该如何检索像975504618748383289这样的数字呢?

When I insert a DOUBLE, why do I see a value like 9.755046187483832e17 when I select that value? How can I retrieve a number like 975504618748383289 instead?

推荐答案

您可能正在寻找 ROUND 函数:

You're probably looking for the FORMAT or ROUND function:

使用FORMAT()，具体取决于您的语言环境和您的特定需求，您可能需要替换千位分隔符:

Using FORMAT(), depending on your locale and your specific needs, you might have to replace the thousands-separator:

mysql> SELECT FORMAT(9.755046187483832e17,0);
975,504,618,748,383,200

mysql> SELECT REPLACE(FORMAT(9.755046187483832e17,0), ',','');
975504618748383200

另一方面，ROUND()是数字函数，它仅输出数字:

On the other hand, ROUND() being a numeric function, it only outputs digits:

mysql> SELECT ROUND(9.755046187483832e17,0);
975504618748383200

请参见 http://sqlfiddle.com/#!2/d41d8/17614 玩这个.

编辑:您注意到，最后两位数字四舍五入为 00 .这是因为 DOUBLE 精度限制.您必须记住 double 是近似.如果您需要比16位精度double更高的精度值和/或更多位数，则可能需要将列的类型更改为

As you noticed, the last two digits are rounded to 00. That's because of DOUBLE precision limits. You have to remember that double are approximate. If you need precise values and/or more digits than available with the 16-bits precision of double, you probably needs to change your column's type to DECIMAL. By default DECIMAL has 10 digits precision (10 base 10 digits). You could explicitly request up to 65 digits.

例如，如果您需要20位数的精度，则可以这样写:

For example, if you need up to 20 digits precision, you write something like that:

CREATE TABLE tbl (myValue DECIMAL(20), ...

请参见 http://dev.mysql.com /doc/refman/5.6/en/fixed-point-types.html

但是请注意，事情不是那么简单.选择小数点列可能会将其默默地转换为 double (或 bigint ?)，从而失去了额外的精度.您可能必须显式转换为字符串，才能保留全部精度.这意味着您可能必须在应用程序级别进行处理.

Please note however than things are not that simple. Selecting the decimal column might silently convert it to double (or bigint ?) thus loosing the extra precision. You might have to explicitly cast to string in order to preserve the full precision. That means the you might have to deal with that at application level.

create table tbl (dblValue DOUBLE, decValue DECIMAL(20,0));
insert into tbl values (975504618748383289, 975504618748383289);

SELECT dblValue, decValue FROM tbl;
--> DBLVALUE            DECVALUE
--> 975504618748383200  975504618748383200

SELECT CAST(dblValue AS CHAR), CAST(decValue AS CHAR) FROM tbl;
--> CAST(DBLVALUE AS CHAR)  CAST(DECVALUE AS CHAR)
--> 9.755046187483832e17    975504618748383289

请参见 http://sqlfiddle.com/#!2/d5f58/2 例如.

这篇关于关闭科学记数法MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！