numerical data types, best choice?

lajkonik86

After reading the manual it is not clear to me which data type I should use to store the following

numbers between
0-1000 (99% of the time will be below 100)
with a precision of 6-8 decimals

Any advice?

mysql numerical data types:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

lajkonik86

actually the decimal precision can go up to 14 at times

laserlight

First, do you need to be safe from floating point inaccuracy? If so, then choose DECIMAL, e.g., DECIMAL(12, 8). If you are fine with floating point inaccuracy, you could consider DOUBLE, e.g., DOUBLE(12, 8). A CHECK constraint could be used to enforce the specific range.

lajkonik86

good I asked, cause my knowledge is failing me.

I'm not entirely sure what the consequences of floating point inaccuracy would be

I'll go look up what the CHECK constraint is immediately

lajkonik86

the data is created by a php script and i don't think i need a check as an additional measure to ensure correct data input. (if i understood correctly what a check is at least )

mpb001

You can read http://uk3.php.net/language.types.float.

Floating point calculations can differ in result when running the same calculations on different machines. It's to do with a speed versus accuracy trade off. Calculations that need exact accuracy ( Money etc) use a decimal handler instead.

lajkonik86

aha 🙂

precision of roughly 14 decimal digits

well 12 will do, so i suppose i am fine with floating point precision

a 0.000000000001 deviation would mess my system

is that going to be a problem?

alternatively i could multiply all number with about a billion or so, would that be a better or worse? (in terms of size, accuracy, speed.. anything😛)

Sxooter

lajkonik86 wrote:
aha 🙂

precision of roughly 14 decimal digits

well 12 will do, so i suppose i am fine with floating point precision

a 0.000000000001 deviation would mess my system

is that going to be a problem?

With floating point yes. Use numeric / decimal (same type, one is an alias for the other.)

Anyone can get the wrong answer fast (i.e. floats)

Note that numeric types (i.e. exact precision) can be used for dozens or even hundreds of places and still get accurate answers.

(edited to add: Ok, with MysQL the math libs they have limit you to 65 digits. postgresql's libs limit you to 1000 digits. Apparently, due to a bug which was discovered early in the 5.0 series, MySQL started limited the numeric types to 65 digits to prevent the bug from showing up.)