1

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?

vitto
  • 19,094
  • 31
  • 91
  • 130

3 Answers3

2

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:

Decimal(3,2) values in MySQL are always 9.99

What does M,D mean in decimal(M,D) exactly?

Community
  • 1
  • 1
micahwittman
  • 12,356
  • 2
  • 32
  • 37
  • thanks, perfect answer, especially with different MySql versions, so do You suggest me tu use different decimal type or one for all? – vitto Dec 01 '09 at 19:54
  • Based on the max values you provided: x_in_grams DECIMAL(4, 2), x_in_milligrams DECIMAL(6, 2), x_in_micrograms MEDIUMINT, x_in_kilojoule SMALLINT – micahwittman Dec 01 '09 at 20:29
0

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

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
-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.

Sergey Kuznetsov
  • 8,591
  • 4
  • 25
  • 22
  • DECIMAL (6, 4) is only correct on older versions of MySQL. More info in my Answer post. – micahwittman Dec 01 '09 at 19:20
  • For any somewhat recent version of mysql, decimal (6, 4) specifies a decimal with a maximum value of 99.9999 as mandated by the SQL standard, NOT 9999.99 (ie, the precision is the full amount od digits including those after the decimal point. – Matthias Winkelmann Apr 04 '16 at 23:28