Model Monetary Data

On this page本页内容

Overview概述

Applications that handle monetary data often require the ability to capture fractional units of currency and need to emulate decimal rounding with exact precision when performing arithmetic. 处理货币数据的应用程序通常需要能够捕获货币的小数单位，并且在执行算术时需要模拟精确的小数舍入。The binary-based floating-point arithmetic used by many modern systems (i.e., float, double) is unable to represent exact decimal fractions and requires some degree of approximation making it unsuitable for monetary arithmetic. 许多现代系统（例如，浮点、双精度）使用的基于二进制的浮点算法无法表示精确的小数，需要一定程度的近似，因此不适用于货币运算。This constraint is an important consideration when modeling monetary data.在建模货币数据时，此约束是一个重要的考虑因素。

There are several approaches to modeling monetary data in MongoDB using the numeric and non-numeric models.在MongoDB中，有几种使用数字和非数字模型对货币数据建模的方法。

Numeric Model数值模型

The numeric model may be appropriate if you need to query the database for exact, mathematically valid matches or need to perform server-side arithmetic, e.g., $inc, $mul, and aggregation framework arithmetic.如果您需要查询数据库以获得精确的、数学上有效的匹配，或者需要执行服务器端算法，例如，$inc、$mul和聚合框架算法，则数字模型可能是合适的。

The following approaches follow the numeric model:以下方法遵循数值模型：

  • Using the Decimal BSON Type which is a decimal-based floating-point format capable of providing exact precision. 使用Decimal BSON类型，这是一种基于十进制的浮点格式，能够提供精确的精度。Available in MongoDB version 3.4 and later.MongoDB 3.4版及更高版本中提供。
  • Using a Scale Factor to convert the monetary value to a 64-bit integer (long BSON type) by multiplying by a power of 10 scale factor.使用比例因子将货币值乘以10比例因子的幂，转换为64位整数（longBSON类型）。

Non-Numeric Model非数值模型

If there is no need to perform server-side arithmetic on monetary data or if server-side approximations are sufficient, modeling monetary data using the non-numeric model may be suitable.如果不需要对货币数据执行服务器端算法，或者如果服务器端近似值足够，则可以使用非数值模型对货币数据进行建模。

The following approach follows the non-numeric model:以下方法遵循非数值模型：

  • Using two fields for the monetary value使用两个字段作为货币值: One field stores the exact monetary value as a non-numeric string and another field stores a binary-based floating-point (double BSON type) approximation of the value.一个字段将精确的货币值存储为非数字string，另一个字段存储该值的基于二进制的浮点（doubleBSON类型）近似值。

Note

Arithmetic mentioned on this page refers to server-side arithmetic performed by mongod or mongos, and not to client-side arithmetic.本页提到的算法是指由mongod或mongos执行的服务器端算法，而不是客户端算法。

Numeric Model数值模型

Using the Decimal BSON Type使用Decimal BSON类型

New in version 3.4.版本3.4中的新功能。

The decimal BSON type uses the IEEE 754 decimal128 decimal-based floating-point numbering format. decimal BSON类型使用IEEE 754 decimal128基于十进制的浮点编号格式。Unlike binary-based floating-point formats (i.e., the double BSON type), decimal128 does not approximate decimal values and is able to provide the exact precision required for working with monetary data.与基于二进制的浮点格式（即doubleBSON类型）不同，十进制128不近似十进制值，并且能够提供处理货币数据所需的精确精度。

From the mongo shell decimal values are assigned and queried using the NumberDecimal() constructor. 使用NumberDecimal()构造函数从mongo shell中分配和查询十进制值。The following example adds a document containing gas prices to a gasprices collection:以下示例将包含天然气价格的文档添加到gasprices集合中：

db.gasprices.insert{ "_id" : 1, "date" : ISODate(), "price" : NumberDecimal("2.099"), "station" : "Quikstop", "grade" : "regular" }

The following query matches the document above:以下查询与上述文档匹配：

db.gasprices.find( { price: NumberDecimal("2.099") } )

For more information on the decimal type, see NumberDecimal.有关decimal类型的详细信息，请参阅NumberDecimal。

Converting Values to Decimal将值转换为十进制

A collection’s values can be transformed to the decimal type by performing a one-time transformation or by modifying application logic to perform the transformation as it accesses records.集合的值可以通过执行一次性转换或在访问记录时修改应用程序逻辑来执行转换，从而转换为decimal类型。

Tip

Alternative to the procedure outlined below, starting in version 4.0, you can use the $convert and its helper $toDecimal operator to convert values to NumberDecimal().除了下面概述的过程之外，从4.0版开始，您可以使用$convert及其助手$toDecimal运算符将值转换为numberdCIMAL()。

One-Time Collection Transformation一次性集合转换

A collection can be transformed by iterating over all documents in the collection, converting the monetary value to the decimal type, and writing the document back to the collection.可以通过迭代集合中的所有文档，将货币值转换为decimal类型，然后将文档写回集合来转换集合。

Note

It is strongly advised to add the decimal value to the document as a new field and remove the old field later once the new field’s values have been verified.强烈建议将decimal值作为新字段添加到文档中，并在验证新字段的值后删除旧字段。

Warning警告

Be sure to test decimal conversions in an isolated test environment. 确保在隔离的测试环境中测试decimal转换。Once datafiles are created or modified with MongoDB version 3.4 they will no longer be compatible with previous versions and there is no support for downgrading datafiles containing decimals.使用MongoDB版本3.4创建或修改数据文件后，它们将不再与以前的版本兼容，并且不支持降级包含小数的数据文件。

Scale Factor Transformation:比例因子转换：

Consider the following collection which used the Scale Factor approach and saved the monetary value as a 64-bit integer representing the number of cents:考虑下面的集合，这些集合使用比例因子方法，并将货币值保存为表示美分数的64位整数：

{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong("1999") },
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong("3999") },
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong("2999") },
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong("2495") },
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong("8000") }

The long value can be converted to an appropriately formatted decimal value by multiplying price and NumberDecimal("0.01") using the $multiply operator. 通过使用$multiply运算符将price和NumberDecimal("0.01")相乘，可以将long值转换为适当格式的decimal值。The following aggregation pipeline assigns the converted value to the new priceDec field in the $addFields stage:以下聚合管道将转换后的值分配给$addFields阶段中的新priceDec字段： 

db.clothes.aggregate(
  [
    { $match: { price: { $type: "long" }, priceDec: { $exists: 0 } } },
    {
      $addFields: {
        priceDec: {
          $multiply: [ "$price", NumberDecimal( "0.01" ) ]
        }
      }
    }
  ]
).forEach( ( function( doc ) {
  db.clothes.save( doc );
} ) )

The results of the aggregation pipeline can be verified using the db.clothes.find() query:聚合管道的结果可以使用db.costs.find()查询进行验证：

{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong(1999), "priceDec" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong(3999), "priceDec" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong(2999), "priceDec" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong(2495), "priceDec" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong(8000), "priceDec" : NumberDecimal("80.00") }

If you do not want to add a new field with the decimal value, the original field can be overwritten. 如果不想添加具有decimal值的新字段，则可以覆盖原始字段。The following update() method first checks that price exists and that it is a long, then transforms the long value to decimal and stores it in the price field:下面的update()方法首先检查price是否存在以及它是否为long，然后将long值转换为decimal并存储在price字段中：

db.clothes.update(
  { price: { $type: "long" } },
  { $mul: { price: NumberDecimal( "0.01" ) } },
  { multi: 1 }
)

The results can be verified using the db.clothes.find() query:可以使用db.costs.find()查询验证结果：

{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberDecimal("80.00") }

Non-Numeric Transformation:非数值转换：

Consider the following collection which used the non-numeric model and saved the monetary value as a string with the exact representation of the value:考虑下面的集合，这些集合使用非数值模型，并将货币值保存为string，其值的精确表示：

{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99" }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99" }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99" }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95" }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00" }

The following function first checks that price exists and that it is a string, then transforms the string value to a decimal value and stores it in the priceDec field:以下函数首先检查price是否存在以及它是否为string，然后将string值转换为decimal值并将其存储在priceDec字段中：

db.clothes.find( { $and : [ { price: { $exists: true } }, { price: { $type: "string" } } ] } ).forEach( function( doc ) {
  doc.priceDec = NumberDecimal( doc.price );
  db.clothes.save( doc );
} );

The function does not output anything to the command line. 该函数不向命令行输出任何内容。The results can be verified using the db.clothes.find() query:可以使用db.costs.find()查询验证结果：

{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99", "priceDec" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99", "priceDec" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99", "priceDec" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95", "priceDec" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00", "priceDec" : NumberDecimal("80.00") }
Application Logic Transformation应用逻辑转换

It is possible to perform the transformation to the decimal type from within the application logic. 可以在应用程序逻辑中执行到decimal类型的转换。In this scenario the application modified to perform the transformation as it accesses records.在这个场景中，应用程序被修改为在访问记录时执行转换。

The typical application logic is as follows:典型的应用逻辑如下所示：

  • Test that the new field exists and that it is of decimal type测试新字段是否存在，是否为decimal类型
  • If the new decimal field does not exist:如果新的decimal字段不存在：
    • Create it by properly converting old field values通过正确转换旧字段值来创建它
    • Remove the old field删除旧字段
    • Persist the transformed record保存已转换的记录

Using a Scale Factor使用比例因子

Note

If you are using MongoDB version 3.4 or higher, using the decimal type for modeling monetary data is preferable to the Scale Factor method.如果您使用的是MongoDB 3.4版或更高版本，那么使用十进制类型来建模货币数据比使用比例因子方法更可取。

To model monetary data using the scale factor approach:要使用比例因子法对货币数据建模，请执行以下操作：

  1. Determine the maximum precision needed for the monetary value. 确定货币值所需的最大精度。For example, your application may require precision down to the tenth of one cent for monetary values in USD currency.例如，您的应用程序可能要求USD货币值的精度降至十分之一美分。
  2. Convert the monetary value into an integer by multiplying the value by a power of 10 that ensures the maximum precision needed becomes the least significant digit of the integer. 将货币值乘以10的幂，将其转换为整数，以确保所需的最大精度成为整数的最低有效位。For example, if the required maximum precision is the tenth of one cent, multiply the monetary value by 1000.例如，如果所需的最大精度为1美分的十分之一，则将货币值乘以1000。
  3. Store the converted monetary value.存储转换后的货币值。

For example, the following scales 9.99 USD by 1000 to preserve precision up to one tenth of a cent.例如，以下比例为9.99美元乘以1000，以保持精度高达十分之一美分。

{ price: 9990, currency: "USD" }

The model assumes that for a given currency value:该模型假设对于给定的货币值：

  • The scale factor is consistent for a currency; i.e. same scaling factor for a given currency.货币的比例系数是一致的；亦即，给定货币的相同比例因子。
  • The scale factor is a constant and known property of the currency; i.e applications can determine the scale factor from the currency.比例系数是货币的常数和已知属性；亦即，应用程序可以根据货币确定比例系数。

When using this model, applications must be consistent in performing the appropriate scaling of the values.使用此模型时，应用程序在执行值的适当缩放时必须保持一致。

For use cases of this model, see Numeric Model.有关此模型的用例，请参阅数值模型。

Non-Numeric Model非数值模型

To model monetary data using the non-numeric model, store the value in two fields:要使用非数值模型对货币数据建模，请将值存储在两个字段中：

  1. In one field, encode the exact monetary value as a non-numeric data type; e.g., BinData or a string.在一个字段中，将精确的货币值编码为非数字数据类型；例如，BinData或string。
  2. In the second field, store a double-precision floating point approximation of the exact value.在第二个字段中，存储精确值的双精度浮点近似值。

The following example uses the non-numeric model to store 9.99 USD for the price and 0.25 USD for the fee:以下示例使用非数字模型存储9.99美元的价格和0.25美元的费用：

{
  price: { display: "9.99", approx: 9.9900000000000002, currency: "USD" },
  fee: { display: "0.25", approx: 0.2499999999999999, currency: "USD" }
}

With some care, applications can perform range and sort queries on the field with the numeric approximation. 只要小心，应用程序可以使用数值近似值在字段上执行范围和排序查询。However, the use of the approximation field for the query and sort operations requires that applications perform client-side post-processing to decode the non-numeric representation of the exact value and then filter out the returned documents based on the exact monetary value.但是，使用近似字段进行查询和排序操作需要应用程序执行客户端后处理，以解码精确值的非数字表示，然后根据精确的货币值过滤掉返回的文档。

For use cases of this model, see Non-Numeric Model.有关此模型的用例，请参阅非数值模型。