How to store Bitcoin values in a MySQL database? floats are bad.... right?

What should I be using to store the values of a wallet in a MySQL database?

Apparently floats are too imprecise to be storing Bitcoins, especially down to the satoshi.

So what should I be using? Decimal ?

satoshi

Wannabee Bitcoin Dev

Posted 2014-03-03T03:32:29.897

Reputation: 165

Answers

There would be small expression errors. If you want to save as integers just multiply by 100000000 and save satoshis.

As @ypercube states MySQL BIGINT is a wise type choice for storing satoshis, MySQL INT does not have a sufficient maximum value.

zaph

Posted 2014-03-03T03:32:29.897

Reputation: 651

I'm not entirely sure I understand your math. – Wannabee Bitcoin Dev – 2014-03-03T05:01:57.897

6@WannabeeBitcoinDev: Store the number of satoshis rather than the number of BTC, since satoshis can't be subdivided. For example, for an amount of BTC 12.531, store the integer 1253100000. For BTC 0.00031, store 31000. – Nate Eldredge – 2014-03-03T05:35:54.330

This is not a good answer. INT datatype in MySQL is restricted up to 2^32-1 for UNSIGNED and from -2^31 to 2^31-1 in SIGNED. BIGINT or DECIMAL is a wiser choice. – ypercubeᵀᴹ – 2014-04-09T10:26:42.883

@ypercube The answer says "integers" (versus floating point as in the question). INT, BIGINT and DECIMAL are all examples of integer types. If someone creating such a table from this answer can't figure out the appropriate integer type on their own, they shouldn't be creating database tables. – dchapes – 2014-04-09T12:40:45.177

@dchapes: If someone knew, they wouldn't have asked a question in the first place. INT and BIGINT are integer datatypes, yes. DECIMAL is a fixed point but not integer. And someone who doesn't know the details, may confuse INTEGER with integer. I only meant this comment to notify the answerer, so he can improve the asnwer. I have not downvoted as I agree with the general notion of the answer. – ypercubeᵀᴹ – 2014-04-09T13:13:13.700

What you want is a MySQL Decimal field.

Specifically you want a DECIMAL(16,8)

That will give you 8 digits before the decimal and 8 after the decimal

See http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html for details.

Steven Potter

Posted 2014-03-03T03:32:29.897

Reputation: 261

What makes you believe the value won't rise above 10^8 USD? :) – Gili – 2018-01-04T09:35:00.227

since there can't be more than 21 millions BTC, (7,8) should be enough. but if there is a possibility of treating additive values, say volume exchanged over a year, then it can go way over. – v.oddou – 2018-03-10T05:54:39.213

storing ohlc base and quote volume in this format explodes the memory big time especially for 1m candles, perhaps keep ohlc float and volumes in double? – PirateApp – 2019-03-01T13:37:00.323

-1

a float natively only goes up to 7 decimals in most cases. if you just roll with decimal you'll be safe. When you round a 7 digit number to 8 decimal places you'll run into rounding issues.

Isuru Fonseka

Posted 2014-03-03T03:32:29.897

Reputation: 149

unusable. floats become very sparse at higher values. the 8 digits after the dot can't be retained after a few hundreds. it becomes integer between 8 and 16 million, and after that you have to jump 2 by 2 until the max 21 million of BTC. however it's possible to possess 19 million BTC and 5 satoshi. not that you would care at this point, but still people's asset can't be waived under the leg. – v.oddou – 2018-03-10T05:57:48.163