Sould I store price as decimal or Integer in Mysql?

Question

I'm currently using decimal to store price data (store $4.2 as 4.20), but it seems having some difficults to comparing equality, i.e. need to calculate the different between 2 numbers(abs(number1 - number 2) < 0.01) rather than using number1 == number2

So I'm wondering if it will be better to store price as integers, say store $4.2 as 420.

Thanks.

Answer 1

You can use what's known as minor currency. Which is basicly storing $4.20 as 420. This is a practice that is commonly adapted when communicating with payment processing gateways or api.

The benefit is that all arithmatic operations in the database and storage happens using integers rather than decimals. Which means they take up less space and operations are faster.

As pointed out by @thilo you also avoid floating point erros that can sometimes creep in.

At the time of display, simply divide by 100

Answer 2

From MySQL 5.7 Reference Manual:

Another way to see the difference in exact and approximate value handling is to add a small number to a sum many times. Consider the following stored procedure, which adds .0001 to a variable 1,000 times.

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE d DECIMAL(10,4) DEFAULT 0;
  DECLARE f FLOAT DEFAULT 0;
  WHILE i < 10000 DO
    SET d = d + .0001;
    SET f = f + .0001E0;
    SET i = i + 1;
  END WHILE;
  SELECT d, f;
END;

The sum for both d and f logically should be 1, but that is true only for the decimal calculation. The floating-point calculation introduces small errors:

+--------+------------------+
| d      | f                |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+

So the problem about which you write is always present when dealing with floating-point type. Indeed, if the amount of stored in MySQL as FLOAT or DOUBLE, you would have this problem, but this is just DECIMAL.

When using a DECIMAL, you can use the usual comparisons in the database. MySQL does't return a false result.

But be careful when you get the data from the database and assign them to variables in a programming language. Especially in a programming language that is dynamic typing of variables...

You should then retrieve the value multiplied by one hundred and operate on integer or long integer... Or better to use special libraries to financial operations on numbers with a comma...