CODEWITHSUNDEEP
mysql
clarkk
clarkk

Reputation: 27685

fields with decimals - which data type to use?

I need a field with percentages

25% => 0.25
30% => 0.30

Which data type do I need to set the field to?

And how can you determine the range of fields with decimals like integer fields? (eg. tinyint unsigned is 0-255)

Edit

I need to do some calculation in my queries

Upvotes: 1

Views: 2713

Answers (5)

xkeshav
xkeshav

Reputation: 54016

use DECIMAL DATATYPE

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For MySQL 5.0.3 and above:

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

UNSIGNED, if specified, disallows negative values.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

note: if you want that that field maximum value between 0-99.99 then use

ALTER TABLE  `table_name` ADD  `percentage` DECIMAL( 5, 2 ) NOT NULL

Upvotes: 2

Paul Sonier
Paul Sonier

Reputation: 39480

You definitely want a DECIMAL type; FLOAT suffers from the same imprecision that all floats do.

Upvotes: 0

Cfreak
Cfreak

Reputation: 19309

How about decimal? :)

You can even set the precision:

CREATE TABLE yourtable ( percentage decimal(10,2) ... );

Upvotes: 0

Alex Rashkov
Alex Rashkov

Reputation: 10015

you should use FLOAT field with at leas 5 bits

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101604

Of the plethora of MySQL numeric types, the FLOAT or REAL may be your best bet (depends on what you're storing).

Upvotes: 1

Related Questions