Choose float from MySQL with correct precision

Question

Common problems I was looking for and reading and still stuck.

I have a trading table where the trading price is declared as a float. I can not use the decimal place, because different stocks have different accuracy in price.

If you look at the table through phpMyAdmin, all the numbers look correct - since they were inserted, as you can see in the screenshot below, the price (p) is 0.709, which is correct.

But when I select this deal from the table using the usual zend framework, I get a price of 0.70899999141693

How to make a choice that works the same way as choosing MyAdmin? And get the number with the right accuracy? If myAdmin does the job, it might be possible in zend, am I right ?

EDIT: I cannot use ROUND , and I cannot use DECIMAL because I do not know the correct precision. for example, the price of Apple has 2 decimal values ​​exactly, but the price of VTB Bank has 6 decimal values ​​exactly

EDIT2: The following are examples of the same choices in myAdmin and zend.

Trade 8838397 was made at a price of 0.01156. I inserted it into the database and what I get in myAdmin:

If I select it with php, I get the following:

I assume that myAdmin somehow guesses the accuracy and does it really well. How???

Answer 1

Quote: “I have a trading table where the trading price is declared as a float. I can not use the decimal, because different stocks have different accuracy in price.”

I suppose it's nonsense. If you need accurate data, use the exact type. You would only use float if the values ​​exceeded the range of DECIMAL clauses, which is not here. DECIMAL (65,30) processes values ​​to 30 digital places. I think this should be enough for all your values.

I assume that you are mixing internal storage with data mapping here. If the number of decimal places is necessary information, save this information:

  trade_id office price precision
 12345678 123456 0.709 4

Inquiry:

 select trade_id, office, round(price, precision) as price from trades; 

Result:

  trade_id office price
 12345678 123456 0.7090

EDIT: Regarding "MyAdmin somehow solved this already, I just can't figure out how":

This is not true. I doubt that it will display 0.120 for entered 0.120 and 0.12000 for entered 0.12000. Dbms will keep the same float value for both, and phpMyAdmin will not be able to say that they need to be treated differently. The only thing phpMyAdmin probably does is round to the maximum number of decimal places, say 6. select round( 0.70899999141693 , 6) leads to 0.709.

Answer 2

You can use the round function:

 select round(p, 3) as p from table_name;