dpxw7293
dpxw7293
2018-06-21 05:35
浏览 442
已采纳

MySQL如何在使用除法时保留精度?

Let us say we have values we've retrieved in the tables, these values are strings so it's fine to divide them. We have this kind of report, but I don't like to handle it thru a Programming Language, yet to run it thru 1 sql query to retrieve all reports.

To make the query short for asking here, take a look at below simple mysql query.

select "68078004281240001301" / "1000000000000000000" from {a dummy table};

The above returns a value 68.07800428124, and we are lossing 0001301 in the decimal point.

Already tried FORMAT() or ROUND() yet not working, or I missed some mysql functions?


But when using bcmath in PHP, we could do it like this

$n = '68078004281240001301';
$d = '1000000000000000000';
bcdiv($n, $d, strlen($d) - 1);

and the value above will give us an exact precision we want 68.078004281240001301


Please remember that I am going to use this for reporting along with filters later on.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dsgdsf12312
    dsgdsf12312 2018-06-21 06:13
    已采纳

    I'm assuming the difficulty is that as your dividing two strings - which are actually integers, it doesn't know the type of values to use (as in precision). If you instead cast the values to the decimal type, you can force it to work to any precision (up to 65 digits)...

    select "68078004281240001301" / "1000000000000000000", cast("68078004281240001301" as decimal(60,30))/ cast("1000000000000000000" as decimal(60,30))
    

    gives...

    "68078004281240001301" / "1000000000000000000"
    68.07800428124
    
    cast("68078004281240001301" as decimal(60,30))/ cast("1000000000000000000" as decimal(60,30))
    68.078004281240001301000000000000
    
    点赞 评论

相关推荐