mysql设置不⽤科学记数法，关闭科学记数法MySQLmysql下载不了怎么办
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?
解决⽅案
You're probably looking for the FORMAT or ROUND function:
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
On the other hand, ROUND() being a numeric function, it only outputs digits:
mysql> SELECT ROUND(9.755046187483832e17,0);
975504618748383200
EDIT: 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.
For example, if you need up to 20 digits precision, you write something like that:
CREATE TABLE tbl (myValue DECIMAL(20), ...
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。