I want to store a decimal number such as 1.35 in a MySQL table column, but I don't understand how to store it in the database. If I set the column type to be an INT, then there is no decimal attached to the value after I insert the value.
I also tried looking at the DECIMAL data type, but that is not working either. Perhaps I am setting it up wrong?
If I want a column in a MySQL table to store a decimal number, what column type should I choose, and what is the proper way to set that up?
To be able to insert a decimal number in MySQL, you need to ensure that the column type is one of the following:
You can add one of these new columns to an existing table with MySQL with something similar to:
ALTER TABLE table_name ADD COLUMN price DECIMAL(4,2);
or if you need to change an existing column:
ALTER TABLE table_name MODIFY COLUMN price DECIMAL(4,2);
Then to actually insert a new row (assuming we have just id and price), it is as easy as:
INSERT INTO table_name (id, price) VALUES (123, 987.44);
However, before just choosing a column type make sure you know if you want a fixed-point or floating-point data type.
DECIMAL is a fixed-point type which means that it stores the exact numeric data values. You will want this when you want to preserve the exact precision, such as with a monetary value (dollars, pounds, etc). It allows for up to 65 significant digits. This is good for business math. The drawback? Performance is worse and they take more storage space. In the SQL syntax, the quotes contain two numbers, the first being the precision, and the second being the scale:
When defining this ensure that you provide enough precision to accommodate the largest numbers you expect. For DECIMAL, precision is the portion before the decimal (does not count digits after decimal). For the scale, this is the portion after the decimal. If you are using money you would simply use 2, but if your needs are different then you might adjust this accordingly.
FLOAT and DOUBLE is a floating-point type that stores approximate numeric data values. You will want to use this when approximation is sufficient. It uses four bytes for float (up to 7 significant digits) and eight bytes for double (up to about 16 significant digits). These are not a good type to use when you need to do exact comparisons. They also suffer from rounding errors which can lead to inaccuracies. They are good for any scientific calculations where values don't need to be exact. They are also great for performance and storage efficiency because they require less processing power and memory. In the SQL syntax, this is similar to how it was done with DECIMAL, but the second parameter is optional (and not a standard syntax). Additionally, the precision (first number in parenthesis) is the total numbers that can be stored (before and after the decimal), and the second number restricts how many can be after the decimal. That is a little bit different than how it works with DECIMAL.
You would want to be looking at inserting a floating-point number such as 3.48. Try using a column of type FLOAT rather than INT.
This is based on the MySQL 5.0 manual, so if you have a lesser version, you'll have to look further into it. 😁
DECIMAL is what you need of course.
As a sample create a table with the field dec that will consist of decimal value:
CREATE TABLE `database`.`table_name` (
`id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
`dec` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`id`)
)
DECIMAL(5,2) means 5 digits before the decimal point and 2 digits after the decimal point.
Thus, you can now store 343.22 or 3.48.
DECIMAL(5,2) means that the precision is 5 which translates to the maximum number of digits that can be stored is 5. The scale here would be 2 which means that the maximum number of digits stored after the decimal place would be 2. Thus the result for this definition would be 3 digits before the decimal place, and 2 digits after the decimal place.
— smiledrops
You can go right to the source:
This section discusses the characteristics of the DECIMAL data type (and its synonyms), with particular regard to the following topics:
- Maximum number of digits
- Storage format
- Storage requirements
- The non-standard MySQL extension to the upper range of DECIMAL columns
The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:
M is the maximum number of digits (the precision). It has a range of 1 to 65.
D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
If D is omitted, the default is 0. If M is omitted, the default is 10.
You can also search the manual for more information.
-
0
You could also store it as an
-
0
Double or float should do it.
— Abdussamad
add a commentintand multiply any values you want to store by 100 first and divide by 100 whenever you retrieve values. However, that's not the right way to do things since there is a specific feature for this. — Mas Sehguh