mysql - Column type for saving very different number of decimals -

i need store numbers like

21000 1.0002 0.00230235 12323235 0.2349523 

this sensordata important keep exact value. there many options.

my solution multiply values 1 million, , store them bigint. make sense?

that makes sense i'd recommend use decimal datatype: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

if multiply million , if dataset receive has 1 more decimal you'd expect, you'd end multiplying number 10 million , other numbers 10. instead, using decimal datatype give 30 numbers right of decimal.

the declaration syntax decimal column decimal(m,d). ranges of values arguments in mysql 5.7 follows:

m maximum number of digits (the precision). has range of 1 65.

d number of digits right of decimal point (the scale). has range of 0 30 , must no larger m.

and

the sql standard requires precision of numeric(m,d) m digits. decimal(m,d), standard requires precision of @ least m digits permits more. in mysql, decimal(m,d) , numeric(m,d) same, , both have precision of m digits.

for full explanation of internal format of decimal values, see file strings/decimal.c in mysql source distribution. format explained (with example) in decimal2bin() function.

to format numbers, formatting answer describes: format number 2 decimal places

example

create table test (   price decimal(40,20) );  -- above insertions succeed cleanly insert test values (1.5), (1.66), (1.777), (1.12345678901234567890);  -- notice have 21 digits after decimal -- mysql insert data 20 decimal , add warning regarding data truncation insert test values (1.123456789012345678901); 

data

select * test  price 1.50000000000000000000 1.66000000000000000000 1.77700000000000000000 1.12345678901234567890 1.12345678901234567890  select cast(price decimal(40,2)) test  price 1.50 1.66 1.78 1.12 1.12