How to Declare and Use the MySQL DECIMAL Data Type

What is a MySQL DECIMAL?  Simply put, it is just a number that can have a decimal in it (2012.56 and -2013.0034 are examples).  Let’s take a look at how to declare a MySQL DECIMAL.

DECIMAL(M, D)

M is the maximum number of digits (IE precision) and has a range from 1 to 65 (Take note that versions of MySQL prior to 5.0.3 allowed the range from 1 to 254).  This is the total number of digits INCLUDING decimal digits.

D is the number of digits to the right of the decimal point (IE scale) and has a range from 0 to 30 AND cannot be larger than M.

Why use a MySQL DECIMAL?

You would generally use a decimal type when you need to store exact fractional values like money.  It doesn’t suffer from the rounding errors of other number types in MySQL.

How do you use MySQL DECIMAL?

Here is some example mysql code…

CREATE DATABASE IF NOT EXISTS Testing;
USE Testing;
CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
SELECT * FROM WeightCalculation;
DROP DATABASE Testing;

Results

WeightCalculationID|Weight
1	54.34450
2	928.23017
3	982.57467

So let walk through this code and see what is going on.  To start, I create our Testing database if it doesn’t exist.  Then I create a table called ‘WeightCalculation’ and give it two columns ‘WeightCalculationID’ and ‘Weight’ in order to store some simple data.  Notice that column ‘Weight’ has been declare a DECIMAL of total length 10 digits and up to 5 decimal places.  Next, I insert two rows of data into the table.  Just for fun I calculate the sum of all the Weight column values and store them in a user-defined variable named @a then insert the sum into another row of the table.  Lastly, I perform a SELECT operation on the table to get it’s contents.  To clean up I drop the Testing database.

For further information check here.  Check here for more examples of precision math.

How to declare and use MySQL DECIMAL
How to declare and use MySQL DECIMAL

Leave a Reply

Your email address will not be published.