The MySQL DECIMAL data type is one of the numbers data types in MySQL that can store numbers with decimal points.
The DECIMAL type requires more storage space than FLOAT or DOUBLE because DECIMAL stores the exact representation of your number value.
To see how the DECIMAL type differs from DOUBLE, let’s create a table with numbers data as an example.
Suppose you have a numbers table in your database with
the following data:
+------------+-------------+
| num_double | num_decimal |
+------------+-------------+
| 2.5 | 2.50000 |
| 0.01 | 0.01000 |
| -1.5 | -1.50000 |
+------------+-------------+
Now let’s try to increment each value in the columns above by 0.2 as follows:
SELECT (num_double + 0.2), (num_decimal + 0.2) FROM numbers;
The returned result set is as shown below:
+---------------------+---------------------+
| (num_double + 0.2) | (num_decimal + 0.2) |
+---------------------+---------------------+
| 2.7 | 2.70000 |
| 0.21000000000000002 | 0.21000 |
| -1.3 | -1.30000 |
+---------------------+---------------------+
As you can see, there’s a problem with the DOUBLE type when you calculate some numbers with decimal points like 0.01 + 0.1.
This is because DOUBLE type numbers are stored as approximate representations. DOUBLE
type can store a larger range of possible numbers than DECIMAL, but there will be precision bugs when you need to do calculations.
DECIMAL types are commonly used for number data with many decimal points that are going to be calculated further such as financial or monetary data.
FLOAT and DOUBLE can be used for decimal numbers that doesn’t require further calculation and are mostly final, such as everyday weather report.
When you create a DECIMAL column, you need to specify the precision digits available for the column (M) and the amount of scale reserved for the decimal points (D)
The M number range is 1 - 65 while the D number ranges from 0 - 30 and must not be larger than M.
For example, the following num_decimal column can contain 10 digits with 5 decimal points:
CREATE TABLE `numbers` (
`num_decimal` DECIMAL(10,5) DEFAULT NULL
)
The maximum range of the
num_decimal column above is 99999.99999 and -99999.99999.
When you don’t specify the range, then MySQL may default to (10,0) as the length parameters of the column.
And that’s how the MySQL DECIMAL type works. The data type is best for when you have numbers with decimal points that require exact representation.
MySQL decimal data type can be used to store the exact numerical value. The syntax of DECIMAL data type.
yourColumnName Decimal(integerValue,intgerValue);
Example of DECIMAL data type.
mysql> create table EmployeeInformation
-> (
-> EmpId int auto_increment primary key,
-> EmpName varchar(200),
-> EmpSalary DECIMAL(6.2)
-> );
Query OK, 0 rows affected (0.54 sec)
Whenever we insert a value in “EmpSalary” column greater than 6 digits, it raise an error. The error is as follows −
mysql> insert into EmployeeInformation(EmpName,EmpSalary) values('John',6999999.50);
ERROR 1264 (22003): Out of range value for column 'EmpSalary' at row 1
Inserting value in “EmpSalary” column with exact number of digits.
mysql> insert into EmployeeInformation(EmpName,EmpSalary) values('John',6999.50);
Query OK, 1 row affected, 1 warning (0.15 sec)
mysql> insert into EmployeeInformation(EmpName,EmpSalary) values('John',69999.50);
Query OK, 1 row affected, 1 warning (0.20 sec)
To display all records.
mysql> select *from EmployeeInformation;
The following is the output.
+-------+---------+-----------+
| EmpId | EmpName | EmpSalary |
+-------+---------+-----------+
| 1 | John | 7000 |
| 2 | John | 70000 |
+-------+---------+-----------+
2 rows in set (0.00 sec)
Updated on 30-Jul-2019 22:30:23
- Related Questions & Answers
- How to store decimal in MySQL?
- How to insert DECIMAL into MySQL database?
- MySQL - CAST DECIMAL to INT?
- How to format number to 2 decimal places in MySQL?
- MySQL SUM function to add decimal values
- How to use Coalesce in MySQL?
- How to store the PayPal decimal amount in the MySQL database?
- Display 3 decimal places in MySQL?
- Use decimal in where clause in SAP ABAP
- How to Convert Binary to Decimal?
- How to Convert Decimal to Binary?
- How to Convert Decimal to Octal?
- How to Convert Decimal to Hexadecimal?
- How to Convert Hexadecimal to Decimal?
- How to get number located at 2 places before decimal point MySQL?
What is the datatype for decimal in MySQL?
In MySQL, NUMERIC is implemented as DECIMAL , so the following remarks about DECIMAL apply equally to NUMERIC . MySQL stores DECIMAL values in binary format. See Section 12.25, “Precision Math”.
What is a decimal datatype?
The decimal data type is a machine-independent method that represents numbers of up to 32 significant digits, with valid values in the range 10 -129 - 10 +125. The DECIMAL data type can take the following two forms: DECIMAL( p ) floating point.
What is the difference between numeric and decimal in MySQL?
NUMERIC determines the exact precision and scale. DECIMAL specifies only the exact scale; the precision is equal or greater than what is specified by the coder.
