17

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 ?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
5
  • I'm not entirely sure I understand your math.
    – Wannabee Bitcoin Dev
    Mar 3, 2014 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, 2014 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, 2014 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, 2014 at 12:40
  • 1
    @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, 2014 at 13:13
19

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.

Improve this answer
4
  • 2
    What makes you believe the value won't rise above 10^8 USD? :)
    – Gili
    Jan 4, 2018 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, 2018 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, 2019 at 13:37
  • @Gili The question is about storing an amount of BTC, not how much BTC is worth in fiat. Since the max amount of bitcoin possible is 21,000,000 8 digits before the decimal is sufficient.
    – antriver
    Jan 8, 2021 at 19:59
-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.

Improve this answer
1
  • 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, 2018 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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