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 ?

Wannabee Bitcoin Dev
  • 195
  • 1
  • 1
  • 6

3 Answers3

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.

Steven Potter
  • 291
  • 1
  • 2
  • 2
    What makes you believe the value won't rise above 10^8 USD? :) – Gili Jan 04 '18 at 09: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 05: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 01 '19 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 08 '21 at 19:59
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.

zaph
  • 661
  • 1
  • 5
  • 17
  • I'm not entirely sure I understand your math. – Wannabee Bitcoin Dev Mar 03 '14 at 05: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 03 '14 at 05: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 09 '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 09 '14 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 09 '14 at 13:13
-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
  • 149
  • 2
  • 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 05:57