CODEWITHSUNDEEP
mysqlsql
Raptor
Raptor

Reputation: 54212

Data Type for storing high precision decimals in MySQL

Similar to this question, I have a CSV of currency rates which up to 9 decimal places accuracy.

For example : 0.558659218 , 4470.011076 , 7.02E-05, 0.000641138, 20832.46989

Which data type should I use for the column ?

I tried FLOAT , DOUBLE and DECIMAL(11,11), they produce one of the following warnings:

  1. Out of range value for column 'column_name' at row X
  2. Value truncated for column 'column_name' at row X

when I use SHOW WARNINGS command.

FYI, The SQL statement is as follow (but I guess it is not related):

LOAD DATA LOCAL INFILE 'currency.csv' INTO TABLE the_currency FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (currency_code, currency_buyin, currency_buyout);

Sample data of the CSV is as follow:

PHP,41.46218559,0.024118362
PKR,95.71731228,0.010447431
PLN,3.2056255,0.311951599
PYG,4470.011076,0.000223713
QAR,3.641148462,0.274638623
RON,3.524472347,0.283730415
RSD,87.59544136,0.011416119
RUB,31.41607934,0.031830834
RWF,626.1686594,0.001597014
SAR,3.750383024,0.266639432
SBD,7.130814403,0.140236436
SCR,13.08102784,0.076446592
SDG,4.412494807,0.226629162
SEK,6.683528257,0.149621571
SGD,1.221878378,0.81841206
SHP,0.623503208,1.603840987
SLL,4349.905174,0.00022989
SOS,1615.486542,0.000619009
SPL,0.166666667,6
SRD,3.274628066,0.305378193
STD,18949.99968,5.28E-05
SVC,8.75,0.114285714

MySQL version: 5.5.34-0ubuntu0.12.04.1 and I'm working in console command.

Upvotes: 0

Views: 2637

Answers (2)

Thomas W
Thomas W

Reputation: 14164

You need DECIMAL(22,11) if you want 11 digits before as well as after the decimal place. DECIMAL(11,11) doesn't have any storage for digits before the decimal place, and is scarcely even valid at all.

For storing decimal or currency values, DECIMAL or NUMERIC (largely equivalent) are the way to go. Conversion rates are conceptually a little bit different -- since occasionally, they could be variable by much wider factors -- but DECIMAL could be a reasonable place to start, since at least it gives you accurate decimal math results.

The alternative would be DOUBLE (FLOAT does not have very good precision), which would allow conversion rates between hyper-inflated currencies to be stored; however, mixing float-point scaling into 'decimal' math requires a well-defined rounding strategy on output.

Upvotes: 9

noobdeveloper
noobdeveloper

Reputation: 420

Decimal is what you need. The parameters are length then decimal places. Decimal(length,decimal place)

Example decimal(4,2) = max value is 99.99 Decimal(6,2) = max value is 9999.99

Hope this helps.

Upvotes: 2

Related Questions