14

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 ?

9

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.

  • I'm not entirely sure I understand your math. – Wannabee Bitcoin Dev Mar 3 '14 at 5:01
  • 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 Mar 3 '14 at 5:35
  • 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ᵀᴹ Apr 9 '14 at 10:26
  • @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 Apr 9 '14 at 12:40
  • @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ᵀᴹ Apr 9 '14 at 13:13
16

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.

  • What makes you believe the value won't rise above 10^8 USD? :) – Gili Jan 4 '18 at 9:35
  • 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 Mar 10 '18 at 5:54
  • 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 Mar 1 at 13:37
-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.

  • 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 Mar 10 '18 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.