Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How should the MySQL Decimal datatype be used in php? [duplicate]

As commonly discussed, (for example here Storing 0.00001 in MySQL ) the DECIMAL data-type should be used for fields where precision / correctness is required, such as an account balance.

I was wondering however, how PHP handles these values and, if they are internally handled as floats, if there is still a problem when reading these values from the database, doing some calculations and writing them back again. If so, how can we force PHP to keep precision in tact?

like image 739
Alex Avatar asked Jan 13 '12 10:01

Alex


People also ask

Which data type is used for decimal numbers in MySQL?

MySQL stores DECIMAL values in binary format. See Section 12.25, “Precision Math”.

How do I get 2 decimal places in MySQL?

The ROUND() function rounds a number to a specified number of decimal places.

What does decimal mean in MySQL?

In MySQL, the DECIMAL and NUMERIC data types store exact, fixed-point values. In MySQL, NUMERIC is implemented as a DECIMAL, so a NUMERIC and DECIMAL are the same data type. This data type is used when it is important to preserve the exact precision, such as storing money data.


2 Answers

The variable is probably a string initially in PHP (when read from the MySQL result object). In general, PHP's floating-point datatype cannot be relied upon to keep the precise decimal value required. You should use an arbitrary-precision mathematics library like GMP. (When you fetch a row of the result object, pass the DECIMAL column value in to the appropriate constructor, and then operate on it using the functions provided by the library you are using.)

To go more into depth: Suppose you have an amount stored in the database, in a DECIMAL(6, 4) column. You want to fetch that into PHP, to do something with it. You issue a query to fetch that column. You fetch the first row of the query into an associative array. Suppose that the value from that row is 2.5674. Your array is now something like array('MyDecimal' => '2.5674') (the number appears as a string). You use (as far as I can tell) gmp_init() to convert that string to a GMP resource. Now you can do mathematics with that number using the other GMP functions. If you want to store a GMP number, you could convert it back to string using gmp_strval() (perhaps you do not have to do this if you are using a database abstraction layer that can handle GMP resources).

like image 92
Hammerite Avatar answered Oct 19 '22 22:10

Hammerite


You could try using the arbitrary precision features:

http://php.net/manual/en/book.bc.php

Your other option is to store the values as INTs and then convert them when they need to be displayed (i.e. divide by one hundred).

like image 45
pwaring Avatar answered Oct 20 '22 00:10

pwaring