MongoDB - What about Decimal type of value?

问题:

I am currently learning and applying MongoDB for a small financial related project.

---

When I read MongoDB in Action, it says:

The only other issue that commonly arises with BSON numeric types is the lack of decimal support. This means that if you’re planning on storing currency values in MongoDB, you need to use an integer type and keep the values in cents.

---

My financial related product will involve some currency values, but I am little bit confused or worried about the above statement. Here are my questions:

1. Can I use double for those currency values in my project?
2. What will happen or is the consequences if I directly use double for them?
3. If decimal type is an must-have thing for financial product, is that a bad idea to use MongoDB?
4. What does it mean you need to use an integer type and keep the values in cents? Does it mean that if I am going to store 1.34 dollars, then I should store 134 cents?

Thanks

回答1:

If you want an exact representation for financial purposes, then doubles or floating point values are unsuitable as the fractional parts are subject to rounding error. Certain decimal values cannot not be represented using binary-based floating points and must be approximated.

For a less technical intro, see The trouble with rounding floating point numbers; if you want to geek out, then read What Every Computer Scientist Should Know About Floating-Point Arithmetic.

The recommendation of using an integer type (storing the value in cents) is to avoid potential rounding errors. This approach is described as "Using a Scale Factor" in the MongoDB documentation for modelling monetary data and is a general workaround for MongoDB 3.2 and earlier.

MongoDB 3.4 includes a new Decimal BSON type which provides exact precision for manipulating monetary data fields.

回答2:

When you don't want to store currency as cent-values, you could store a currency of $1.34 as an object like this:

{
    major: 1,
    minor: 34,
    currency: "USD"
}

Doing any math with objects like this would not be easy and would not use commercial rounding rules. But you shouldn't do any business logic on the database anyway, especially not when it's a "dumb" database like MongoDB.

What you should be doing is serialize/deserialize these objects from/to a Money class in your application which implements the basic currency math operations respecting the rounding rules and throws an exception when you try to do an operation with different currency units ($12.34 + 14.95€ = error - must convert one currency to the other currency first by providing an exchange rate).

回答3:

If you're using Mongoose then you can use getter/setter functions in the schema definition e.g.

function getDecimalNumber(val) {    return (val/1000000); }
function setDecimalNumber(val) {    return (val*1000000); }

Applicable to a schema object such as

balance: { type: Number, default: 0, get: getDecimalNumber, set: setDecimalNumber },

The number of zeroes to multiply/divide by depends on the accuracy you want.

回答4:

Looks like MongoDB has finally added support for decimals, although at the time of writing this is just finished development, but hopefully it should be available very soon in stable version (3.4?).

https://jira.mongodb.org/browse/SERVER-1393

回答5:

I know this post is old but it ranks high on Google so...

The best solution for storing financial data is using exact precision as documented by MongoDB themselves here http://docs.mongodb.org/v2.6/tutorial/model-monetary-data/#monetary-value-exact-precision.

{price: 9990, currency: "USD" }

And when you need the data just divide by 100 assuming you want 2 digit precision. The downside is that you always need to work with the same precision.

回答6:

MongoDb added support for Decimal data type in 3.4 version. It is also available from the shell.

3.4 adds support for the decimal128 format with the new decimal data type. The decimal128 format supports numbers with up to 34 decimal digits (i.e. significant digits) and an exponent range of −6143 to +6144.

Unlike the double data type, which only stores an approximation of the decimal values, the decimal data type stores the exact value. For example, a decimal NumberDecimal("9.99") has a precise value of 9.99 where as a double 9.99 would have an approximate value of 9.9900000000000002131628...