Best mysql datatype for grams, milligrams, micrograms and kilojoule

Question

Hello, I'm very dubious about datatype of my database, I need to use different unit of measurements because I have different elements and different scales, and I'm sure about the limits used, because I take them from standard elements, but I'm afraid about the variety.

Just consider I work with 100 grams of product and take from it all elements, so, I'm sure I can't go over 100 grams for proteins, but i can reach 3500 kilojoule or 3,27 milligrams of iron element.

So I need various datatypes I think, but I'm not sure about the right kind.
I'll show the max values to be more clear about the unit limits:

grams          99,99 g
milligrams   9999,99 mg
micrograms  99999    µg
kilojoule    9999    kj

So, what is the right way?
I'm sure the best way (the best performance) is to store the various elements with their standard values (for example, kilojoule for calories) but what are the datatype equivalents?

Answer 1

I think that you can choose DECIMAL data type for such data. You can specify how many symbols need to store before comma and after. Like this:

DECIMAL (6, 4)

means that you can store maximum 999999.9999 value in this field.

Answer 2

Make use of MySql DECIMAL with precision as required. FLOAT might not give you the precision required to store the values exactly.

Answer 3

For a 999999.9999 max decimal in the current version of MySQL, you would need to define field as:

DECIMAL (10, 4)

(it's easily confused with DECIMAL (6, 4) which was correct in older versions of MySQL, but is incorrect on the latest)

SEE:

http://stackoverflow.com/questions/1523173/decimal3-2-values-in-mysql-are-always-9-99/1525090#1525090

http://stackoverflow.com/questions/1818046/what-does-m-d-mean-in-decimalm-d-exactly/1818057#1818057