Skip to content
1.2
Decimals
1.4

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.

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

Then we'll insert some data into the table:

SQL
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:

SQL
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:

SQL
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.

About this lesson

There are two different ways to store numbers with decimal parts: DECIMAL and FLOAT. Depending on your needs, one type may fit better than the other.

05:27
Closed captioned

Meet your instructor, Aaron Francis

Developer Education

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.