CODEWITHSUNDEEP
mysqlfloating-pointdecimal
rg88
rg88

Reputation: 20977

MySQL - Storing numbers, greater than 1, with a large number of decimal places

I need to use an accurate number that has many decimal places. If I use the type decimal (because float is not precise enough to store such numbers) I can have something like 0.003352466 and it is preserved accurately. However if I make that number larger than 1, I cannot do it 1.003352466 will fail to be stored.

How do I store both types of numbers in one column?

Like, if one row has the value 0.003352466 but the next row needs to store 1.003352466, how do I do this? I have not been able to figure this out.

FYI I had initially tried DECIMAL(10,10) and that suffered the same failure as mentioned above.

EDIT:

I have tried specifying DECIMAL(2,10) but that fails as the number on the left must be equal to or greater than that on the right. SO I tried DECIMAL(10,10) and that simply fails to write to the database. As I mentioned above it WILL let me enter 0.003352466 but not 1.003352466

Solution... make the first number larger than the second and it works!

DECIMAL(10,10) will fail to write as mentioned but DECIMAL(10,9) will succeed. Go figure.

Upvotes: 1

Views: 1516

Answers (2)

Wrikken
Wrikken

Reputation: 70490

You probably have a wrong table definition (care to show it?). From the manual:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

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.

So, a DECIMAL(11,9) would work in this case, alter as required.

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425391

Provide the precision to your field's declaration.

DECIMAL(N, M) means "N digits total, with M decimal places".

Say, DECIMAL(10, 2) will allow you to store numbers from 0.00 to ±99999999.99.

Upvotes: 2

Related Questions