Storing money in mysql

Question

I want to store 3.50 in a mysql table. I have a float in which I store it, but it is stored as 3.5, not 3.50. How can I make it have a zero zero?

Answer 1

Do not store monetary values ​​as a float, use the type DECIMAL or NUMERIC:

Documentation for MySQL Numeric Types

EDIT and clarify:

Float values ​​are vulnerable to rounding errors because they have limited precision, so if you don't care that you only get 9.99 instead of 10.00, you should use DECIMAL / NUMERIC since they are fixed-point numbers that do not have such problems.

Answer 2

It is generally a good idea to store money as a float, since rounding errors can occur in calculations.

Use DECIMAL (10.2) instead.

Answer 3

Does it really matter if it is stored as 3,5, 3,50 or even 3,500?

What really matters is how it is displayed after it is retrieved from db.

Or am I missing something?

Also do not use float, use decimal place. Float has all kinds of rounding and not very large.

Answer 4

To save the values, you can use the DECIMAL field (10,2) , then you can use FORMAT :

SELECT FORMAT(`price`, 2) FROM `table` WHERE 1 = 1 

Answer 5

If you use the DECIMAL or NUMERIC types, you can declare them as, for example, DECIMAL (18, 2), which would force 2 decimal places, even if they are 0. Depending on how large the values ​​you expect, you can change value of the first parameter.

Answer 6

Why do you want to store "3.50" in your database? 3.5 == 3.50 == 3.5000 in relation to the database.

Your presentation and formatting of numbers / dates / etc. must be executed in the application, not in the database.

Answer 7

Binary cannot accurately represent floating points with only a limited number of bits. This is not much data loss, but actually conversion errors. Here are examples of manual

You can see this in action in your browser, see for yourself in this piece of code.

 <script> var floatSum = 0; // add 0.1 to floatSum 10 times for (var i=0; i<10; i++) { floatSum += 0.1; } // if the repetative adding was correct, the floatSum should be equal to 1 var expectedSum = 10*0.1; // 1 // you can see that floatSum does not equal 1 because of floating point error document.write(expectedSum + " == " + floatSum + " = " + (expectedSum==floatSum) + "<br />"); // --- using integers instead --- // Assume the example above is adding £0.10 ten times to make £1.00 // With integers, we will use store money in pence (100 pence (also written 100p) in £1) var intSum = 0; // add 0.1 to floatSum 10 times for (var i=0; i<10; i++) { intSum += 10; } // if the repetative adding was correct, the floatSum should be equal to 1 var expectedSum = 10*10; // 100 // you can see that floatSum does not equal 1 because of floating point error document.write(expectedSum + " == " + intSum + " = " + (expectedSum==intSum) + "<br />"); document.write("To display as &pound; instead of pence, we can divide by 100 (presentation only) : &pound;" + intSum/100 + "<br />"); </script>