Storing decimals in MySQL

If you're working on a project that requires the storage of decimal values, you may wonder what the best practice is for MySQL. You don't want to end up with inaccurate or imprecise data, and your choice of data type can make a big difference. In this video, we'll explore the different options you have for storing decimals in MySQL and when to use each one.

The four types of decimal data types

There are four different types of data types in MySQL that can store decimal values:

1. DECIMAL: a fixed-precision data type that stores exact values.
2. NUMERIC: an alias for DECIMAL, the two are the same thing in MySQL.
3. FLOAT: a floating-point data type that stores approximate values.
4. DOUBLE: a floating-point data type that stores larger and more precise values than FLOAT.

Each of these data types has its own use case, and which one you choose depends on the precision and accuracy you require.

When to use decimal

If you need to store values that require absolute precision, such as currency or other financial data, you should use the DECIMAL data type. With DECIMAL, you can specify the maximum number of digits and how many digits should occur after the decimal point.

For example, suppose you want to store a maximum of 10 digits, with two digits after the decimal, the syntax would be:

DECIMAL(10,2)

The first argument specifies the maximum number of digits, while the second argument specifies how many should appear after the decimal. The number of digits before the decimal is determined by the first value subtracted by the second.

When to use float or double

If you don't require precise decimal values, you can use either FLOAT or DOUBLE. Both of these data types store approximate values, but DOUBLE can store larger and more precise values than FLOAT.

If you're using a data type for scientific calculations, where relative precision is more important than absolute precision, you might also consider using FLOAT or DOUBLE.

Example

Let's see how these data types work in action. We'll create a table called decimals and insert data into two columns D1 and D2. Both columns will be defined as DOUBLE data types.

CREATE TABLE decimals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  D1 DOUBLE,
  D2 DOUBLE,
);

Then we'll insert some data into the table:

INSERT INTO decimals (D1, D2)
VALUES (100.4, 20.4), (-80.0, 0.0);

If we run a SELECT query to retrieve all of the data in the table:

SELECT * FROM decimals;

We should see the following output:

| id | D1 | D2 |
|----|----|----|
| 1  | 100.4 | 20.4 |
| 2 | -80.0 | 0.0 |

Now, if we try to add the values in columns D1 and D2, we might expect the result to be 40.8, but that's not what we get:

SELECT SUM(D1), SUM(D2) FROM decimals;

| SUM(D1)           | SUM(D2)    |
|-------------------|------------|
| 20.4000000000006  | 20.4       |

We can see that the sums are close to the expected result, but not exactly the same, and that's because DOUBLE is an approximation of a value, not an exact one.

Conclusion

When working with decimal values in MySQL, it's important to choose the correct data type for your needs. If you require absolute precision in your values, DECIMAL is the best choice. If you don't require exact values, FLOAT or DOUBLE may be more appropriate. Which one you choose just depends on the range of data you're storing and the amount of precision you need.

Remember, when working with FLOAT or DOUBLE, results will not be exactly precise, and values may be slightly different from what you expect.