MySQL FLOAT vs DEC: working with fraction and decimal [DEC]

MySQL DataTypes

• MySQL data types: CHAR, VARCHAR, INT, TEXT #Part-1
• MySQL date and time DataTypes Overview: DATE, TIME, DATETIME, TIMESTAMP, YEAR & Zero Values #Part2.1
• Automatically insert Current Date and Time in MySQL table #Part – 2.2
• MySQL: Working with date time arithmetic #Part 2.3.1
• MySQL FLOAT vs DEC: working with fraction and decimal [DEC]

So far we have covered many of of useful datatypes of MySQL. Some of them are integers, text, varchar and most importantly, date and time and date-time manipulation. Now, let us proceed to the next useful sets of datatypes of MySQL. In this chapter, we will cover one of the most useful datatypes needed in real web applications to manage scores, numerical values in form of floating decimals.

Unlike integers, we sometime need to store fractional values to the database. Suppose, you are to store score of a student to a database. The score can be in fraction as well. So, if you define the score column in integer, then what will happen is, the decimal part of the number will get stripped off and only the integer value will get stored. Obviously you wont want that to happen! So, now let us how we can use the powerful decimal & float datatype of MySQL to store fractional numericals on the database…

MySQL FLOAT vs DEC (concept analysis):

One may get confused that decimal and float both are the same. In terms of mathematics they are same but not in terms of memory and precision. FLOAT stores numbers in approximate precision. Where as DECIMAL stores in exact and exactly the same precision defined before. If you try to change the precision of a float column, then you may see some change in the number after the decimal point. Whereas, if you do this in decimal then there won’t be such changes.

Practically, it is better to use DECIMAL, for storing exact datas. It very useful for monetary stuffs. Use float only when you can live with some approximation. Personally I recommend using DECIMAL only. To be more precise,

• In case of decimal you have to define the decimal point precision. Each of definition will be treated as different datatype. So, storing the numbers 14.35 and 14.356 should be done through different definition of double.
• But in case of float, you don’t actually need to mention the point precision. If you have defined a float to store a 6 digit number, then it is capable of storing 14.35 as well as 14.356.
• In case of double, if you store 14.35 on a column having 3 decimal precision, then it will be stored as 14.350, but this is not the case for float.

Although, as of MySQL 5 (Not sure, whether it was there in MySQL 4 or not), it provides a non-standard syntax of storing precision definition for float as well, but I recommend to stick with the standard SQL behavior of float and double.

I hope, it clears your doubt between float and decimal. As of our recommendation, we will use DECIMAL only through out our tutorial.

The syntax of DECIMAL datatype of MySQL:

Just like any other MySQL datatypes, we need to define it during the creation or alteration of a MySQL table.

The default syntax of DECIMAL is

Where, m is the total number of digits and n is the number of digits after the decimal point. Say, you want to store some numbers like the format 9999.9099999. Here the value of m is 11 and that of n is 7. So the syntax would be,

Now, let us see some practical example.

Examples:

Say, you want to create the following table:

The columns id and name are INT and VARCHAR respectively. Where as, the salary and score columns are the DECIMAL of 2 and 1 precision respectively. We execute the following SQL command from the terminal. Make sure that you have created and used a database.

CREATE TABLE `salary_info`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL default '',
`salary` DEC(6,2) NOT NULL default 0,
`score` DEC(3,1) NOT NULL default 0,
PRIMARY KEY ( `id` )
);

This would result in this:

Now, to dump the data, we execute the following command:

INSERT INTO `salary_info` (name, salary, score) VALUES
('Swashata', 8999.90, 36.1),
('Mike', 9000.00, 80.2);

All done, now let us view the table, using SELECT command:

SELECT * FROM salary_info;

This results in something like this…

Hope, that gives you the exact idea of how to work with MySQL Decimal datatype. Now, for experiment let us execute the following command:

INSERT INTO salary_info (name, salary, score) VALUES
('Your_name', 567.3, 78),
('John', 78897.9867, 100.987);

SELECT * FROM salary_info;

This results in something like this:

Quite interesting right? Basically there two things to remember when inserting a number not in accordance with the DECIMAL definition:

• If the number is lower than the range, then it will get exactly the same representation by getting padded with sufficient ZEROs [0] before the digits and after the decimals.
• If the number is greater than the range, then it will be rounded off to the maximum or minimum range.

Range of Decimal:

The range of decimal varies according to the value of m and n respectively. Say, we have defined DEC(5,2). Then the range is -999.99 to +999.99. The following points are to be remembered while dealing with the range of DECIMAL.

• The actual range of m is, 65. So, it is quite a big number to deal with.
• The default value of m is 10.
• DEC(10) is same as DEC(10,0).

And, that was all about MySQL datatype to store fractional data with DECIMAL. Hope it was useful for you! If you face any problem feel free to ask.

Resources:

• MySQL official Documentation for numeric datatype.
• Difference between FLOAT and DECIMAL.