I found in the SO a question with an answer well accepted by the community #
But I never had problems with double
for monetary calculations, I had yes with float
that already left my hair standing, then from this answer stating that Decimal is correct, or that is only for C # and nothing to do with MySQL, I started to test it, and it left me a little insecure, for example to say that I will always store 2 digits of floating point, there are cases where the user will "need" (exception ) register the value with 4 digits in the bank which in turn will only record the first two, for this reason I feel safer with double
.
Question - There is an example source code that shows me this double "rounding error" for me to see, or a reference book, trusted site that signals this recommendation of Decimal usage to record monetary values in MySQL ?
Obs 1 - The data types I refer to are from MySQL because I have also seen a quote from MSDN #
Obs 2 - Has a MySQL own link link My English is not very good, see if I'm wrong, say:
maximum number of digits for DECIMAL is 65
Maximum number of digits for DECIMAL It is 65
And in the sequence says
Before MySQL 5.0.3, the maximum range of DECIMAL values is the same as for DOUBLE
That is, the largest DECIMAL is equal to DOUBLE .
Continuing the site text
but the current range for a given DECIMAL column can be constrained by the precision or scale for a given column
Is that part of what I understand is DECIMAL that is limited by precision or am I wrong?
I'm going to put a test here, I hope someone will show me this "error" in the same way (similarly)
CREATE TABLE 'teste' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'numero' double NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO 'test'.'teste' ('numero') VALUES ('0.1');
INSERT INTO 'test'.'teste' ('numero') VALUES ('99.9');
SELECT sum(numero) FROM teste;
mysql> SELECT sum(numero) FROM teste;
+-------------+
| sum(numero) |
+-------------+
| 100 |
+-------------+
1 row in set (0.00 sec)