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

Question

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.

Answer 1

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