0

I need to store numbers like

21000
1.0002
0.00230235
12323235
0.2349523

This is sensordata so it is important to keep the exact value. THere are many options.

My solution would be to multiply all values by 1 million, and store them as a bigint. Would that make sense?

Riël
  • 1,251
  • 1
  • 16
  • 31
  • if you don't need to treat those numbers as numbers in the db (e.g. no math/comparisons done on them), then storing as text would make most sense. the decimals won't be affected at all, because mysql won't mangle numbers-as-text. – Marc B Apr 19 '16 at 18:33

1 Answers1

1

That makes sense but I'd recommend that you just use the decimal datatype: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

If you were to multiply by million and if a dataset you receive has one more decimal than you'd expect, you'd end up multiplying that number by 10 million and all other numbers by 10. Instead, using the decimal datatype will give you 30 numbers to the right of the decimal.

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

M is the maximum number of digits (the precision). It has a range of 1 to 65.

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.

and

The SQL standard requires that the precision of NUMERIC(M,D) be exactly M digits. For DECIMAL(M,D), the standard requires a precision of at least M digits but permits more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are the same, and both have a precision of exactly M digits.

For a full explanation of the internal format of DECIMAL values, see the file strings/decimal.c in a MySQL source distribution. The format is explained (with an example) in the decimal2bin() function.

To format your numbers, you could do formatting like this answer describes: Format number to 2 decimal places

Example

create table test (
  price decimal(40,20)
);

-- all the above insertions will succeed cleanly
insert into test values (1.5), (1.66), (1.777), (1.12345678901234567890);

-- notice we have 21 digits after decimal
-- MySQL will insert data with 20 decimal and add a warning regarding data truncation
insert into test values (1.123456789012345678901);

Data

select * from test

price
1.50000000000000000000
1.66000000000000000000
1.77700000000000000000
1.12345678901234567890
1.12345678901234567890

select cast(price as decimal(40,2)) from test

price
1.50
1.66
1.78
1.12
1.12
Community
  • 1
  • 1
zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • As I understand, for DECIMAL I need to set the exact amount of decimals, but those can vary? Or do I understand this wrong? – Riël Apr 19 '16 at 18:49
  • You can create a field called `price decimal (40,20)` and that will store 40 digits of which 20 will be to the right of the decimal point. That way, you can store 1.5, 1.66, 1.777, 1.12345678901234567890 – zedfoxus Apr 19 '16 at 18:55