MySQL – Datatypes for Cryptocurrency

currencymysqlsqlsqldatatypes

The infamous question about datatypes when storing money values in an SQL database.

However in these trying times, we now have currencies that have worth up to 18 decimal places (thank you ETH).

This now reraises the classic argument.

IDEAS

Option 1 BIGINT Use a big integer to save the real value, then store how many decimal places the currency has (simply dividing A by 10^B in translation)?

Option 2 Decimal(60,30) Store the datatype in a large decimal, which inevitibly will cost a large amount of space.

Option 3 VARCHAR(64) Store in a string. Which would have a performance impact.

I want to know peoples thoughts and what they are using if they are dealing with cryptocurrency values. As I am stumped with the best method for proceeding.

Best Answer

There's a clear best option out of the three you suggested (plus one from the comments).

BIGINT — uses just 8 bytes, but the largest BIGINT only has 19 decimal digits; if you divide by 1018, the largest value you can represent is 9.22, which isn't enough range.

DOUBLE — only has 15–17 decimal digits of precision; has all the known drawbacks of floating-point arithmetic.

VARCHAR — will use 20+ bytes if you're dealing with 18 decimal places; will require constant string↔int conversions; can't be sorted; can't be compared; can't be added in DB; many downsides.

DECIMAL(27,18) – if using MySQL, this will take 12 bytes (4 for each group of 9 digits). This is quite a reasonable storage size, and has enough range to support amounts as large as one billion or as small as one Wei. It can be sorted, compared, added, subtracted, etc. in the database without loss of precision.

I would use DECIMAL(27,18) (or DECIMAL(36,18) if you need to store truly huge values) to store cryptocurrency money values.

Related Question