Contents
Which datatype should be taken to store monetary values?
The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.
What numeric data type is best used for prices or financial values?
That’s the reason we should always prefer BigDecimal or BigInteger for financial calculations. Primitive type — int and long are also useful for monetary calculations if decimal precision is not required.
What is the data type for money?
The MONEY data type stores currency amounts. Unlike the DECIMAL data type, the MONEY data type is always treated as a fixed-point decimal number. The database server defines the data type MONEY(p) as DECIMAL(p,2). If the precision and scale are not specified, the database server defines a MONEY column as DECIMAL(16,2).
Is MONEY a datatype in MySQL?
We can store the money values in MySQL in decimal(value1,value2). Here, value1 is the total range including value2. The value2 specifies the number of digits after the decimal point.
What is the best data type for MONEY in mysql?
1 Answer
- You must use fixed-point numeric data type for storing the money values in MYSQL like this:
- Here, 15 is the precision (i.e. total length of value including decimal places) and 2 is the number of digits after the decimal point.
- Note: As money needs an exact representation.
How to store money values in MySQL database?
You must use fixed-point numeric data type for storing the money values in MYSQL like this: decimal(15,2) Here, 15 is the precision (i.e. total length of value including decimal places) and 2 is the number of digits after the decimal point. Note: As money needs an exact representation. So, never use data-type that is only approximate like float.
Which is the best data type for storing currency values?
If you want to be GAAP compliant, you could go with DECIMAL (N,4), where the value of N is at least the number of digits before the . of the greatest sum you ever expect to be stored in that field + 7.
What’s the best way to store cents in a database?
So if you only need to store cents and can safely round up or down, just multiply by 100. In my example, that would make 10023 as the integer to store. You’ll save space in the database and comparing two integers is much easier than comparing two floats. My $0.02. super late entry but GAAP is a good rule of thumb..
What are the decimal values in MySQL database?
Although MySQL lets you use decimal (65,30), 31 for scale and 30 for precision seem to be our limits if we want to leave transfer option open. Why (27,4)?