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:
- DECIMAL: a fixed-precision data type that stores exact values.
- NUMERIC: an alias for DECIMAL, the two are the same thing in MySQL.
- FLOAT: a floating-point data type that stores approximate values.
- 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:
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
DOUBLE. Both of these data types store approximate values, but
DOUBLE can store larger and more precise values than
If you're using a data type for scientific calculations, where relative precision is more important than absolute precision, you might also consider using
Let's see how these data types work in action. We'll create a table called
decimals and insert data into two columns
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
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.
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,
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
DOUBLE, results will not be exactly precise, and values may be slightly different from what you expect.