8

Referred to this question. In the marked answer said that the best way to store cryptocurrency in mysql is to store it as DECIMAL(27,18) or DECIMAL(36,18). But comment there makes me doubt. If ETH value has 32 bytes, then we can't store balance in this way. So what is the best way to store it?

P.S. Except strings, I need to sort wallets by its balance value.

Alexxosipov
  • 1,215
  • 4
  • 19
  • 45
  • 1
    Saving a few tens of bytes per row by using an unnatural data type is a false economy. Let's say you have ten million users in your table. Let's say you save 50 bytes per user in your DBMS with some kind of integer value rather than a gigantic DECIMAL value. You've saved half a gigabyte of storage. If you only have ten thousand users, you've saved half a megabyte of storage. – O. Jones Dec 10 '19 at 22:32
  • 1
    The decimals has the following limits digits=65 and the maximum supported decimals are 30. So it is : DECIMAL(65,30) –  Dec 10 '19 at 22:33

3 Answers3

9

The accepted answer is wrong, very wrong. Mysql rounds decimals in ways that are not apparent in phpMyAdmin. You won't realize what has gone wrong until much later. You will get rounded Satoshi/Wei, and god forbid you have arithmetic going on when you are keep balances of things for a game.

Use ints, two columns, and concatenate. If high efficiency is your goal then you wouldn't be using Mysql. You use Mysql for other reasons in crypto, so don't worry about extra time.

You can also lop off some zeroes to fit in more in a BigInt. Then you can just worry about not allowing dust in your frontend and rounding there.

2021 Edit: The above answer makes me very angry because of the seniority of the account being uses a circular authority to get more seniority. Someone who understands anything at the low level of CS, or read the Mysql manual would not make this mistake. It was probably accepted due to circular authority. It's not to take away from the knowledge of the person posting the answer, but the way authority works in governance and decentralized society.

Just because you are an expert in one thing, does not mean you are at everything else. I suppose the most infuriating thing is the use of the word precision. It does not mean what the person replying thought it did, and this could lead to confusion that causes damage.

Here are some good links to help understand what I am talking about better:

https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html

https://www.soa.org/news-and-publications/newsletters/compact/2014/may/com-2014-iss51/losing-my-precision-tips-for-handling-tricky-floating-point-arithmetic/

Philosophically, this is why it is imperative to allow dissent, even outside the Overton window in society. There are so many process problems in this world (as the pandemic has shown), that have very counterintuitive solutions. However, democratic policy is to find the most popular answers, not always the most efficient ones. When policy, authority, or procedure is based on design by committee, it will inevitably lead to disaster.

Many want to know the answer to this question. It is not a dumb question at all. The answer approved and upvoted even sounds plausible if you don't know the answer. In fact, it sounds very plausible. Probably the answer a group of student in a class would pick collectively as the answer. It could however lead to grave consequences if believed.

I hope those reading this answer see more than just an answer to this question. Skepticism alone can lead you to answers to other problems you face. Don't believe everything you read on the internet, even from smart people who contribute the most.

ThickMiddleManager
  • 266
  • 2
  • 11
  • I'm using MySQL Workbench and not seeing any issues like you mention (although still at the beginning stages). I also read the MySQL documentation (https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html) and don't see anything about decimals being rounded. Are you sure this isn't an issue with phpMyAdmin only? Floating point types are not decimals, so is there maybe some conversion happening between phpMyAdmin and mySQL similar to what your second article mentions (i.e. "our numbers are being converted from decimal into binary floating point")? – Sum None May 19 '22 at 13:53
  • Read my answer again. It says in the first sentence; 'that are not apparent in phpMyAdmin'. This is a MySQL behavior, and it gets masked by phpMyAdmin. – ThickMiddleManager May 22 '22 at 15:23
  • Yes, I read that. It's not the point. The point is: if you're using phpMyAdmin (or whatever tool or language) to do whatever in mySQL, maybe there is a conversion happening somewhere and maybe that's the issue. Otherwise, why even mention phpMyAdmin? It's irrelevant then. Except, "not apparent in phpMyAdmin" makes me think phpMyAdmin is at least part of the issue. I.e. If phpMyAdmin is not showing a correct representation of the decimal in the mySQL db, that's an unwanted issue with phpMyAdmin (and not mySQL), even if there really is also an unwanted issue with decimals rounding in mySQL. – Sum None May 22 '22 at 16:34
  • PhpMyAdmin doesn't display the truth, mySQL doesn't work right for the problem. I mention it because most people will be using phpMyAdmin. It's like a four banger calculator's decimal errors. If you don't know how things work under the hood, you will not understand the rounding is happening. – ThickMiddleManager Jun 09 '22 at 13:36
  • Fair enough, but so far I've only heard anecdotal evidence from you that phpMyAdmin is doing the rounding and not mySQL. I already have a couple apps with long decimal values in mySQL that don't appear to have any rounding issues. I don't use phpMyAdmin. So, I would like to see some evidence that mySQL (only) has an issue. Sorry if I don't take your word for it after you're telling me you're using a UI with an admitted issue. Please enlighten me as to how mySQL is rounding under the hood. I definitely don't understand it, but am able to with any amount of evidence. – Sum None Jun 09 '22 at 17:09
  • Actually, I just fired up phpMyAdmin and don't see any issues in it's interface either and I'm using decimals up to 18 digits. No rounding. I'm flexible, some links or anything would suffice as evidence. – Sum None Jun 09 '22 at 17:29
  • There's nothing to see in the interface. You will not be reconciling the amounts later on. You should never, ever, under any circumstances uses decimals for balances of Ethereum. Store in Wei, do math somewhere else. – ThickMiddleManager Jun 10 '22 at 22:20
  • Right on, I guess I'm going to learn the hard way. I've done my own searching and can't really find anything that supports your claims. Even SO articles with similar claims here: https://dba.stackexchange.com/questions/300214/wrong-value-when-storing-big-float-value/300270#300270 and here: https://stackoverflow.com/questions/5480804/mysql-cant-handle-large-values-even-with-decimal support my suspicion. If I ever get bit, I'll report back. If it were a matter of just sticking in an int type, I would have done that to start, but there's no real great options but to concat like you mentioned. – Sum None Jun 11 '22 at 13:03
  • If you don't get bit first to understand, then please don't do anything with other people's money/assets until you do. Please reference the standard, maybe it will help: https://en.wikipedia.org/wiki/IEEE_754-1985 You cannot get around this problem. It is terrible MySQL doesn't warn people emphatically in the new crypto age where this applies to many more things, but it's open source so there is no obligation to. Floating point operations is just a hard no. The workaround is much less challenging than reconciling something cryptic and then remembering after some hours this thread. – ThickMiddleManager Jun 15 '22 at 04:35
  • How can they emphatically warn people when it doesn't seem to exist (as far as they know)? With that being said, I'm aware of floating point issues, that was never the point (no pun intended) and again, is not an issue with storing decimals in mySQL. You kind of keep making my argument for me, and yet it seems we're arguing apples to oranges at this point? Anyway, I'm not currently doing any computation of floating points, so sounds like I should be good. – Sum None Jun 15 '22 at 08:55
  • I'm well aware of people exhaustively saying an issue exists, but haven't really seen any hard evidence/examples of such or experienced it (that I'm aware). I have another currency project that goes to the fifth decimal place and again, no issues. It's been live for years now and no complaints. Even that bug report you just referenced seems to be asking the question, rather than stating anything emphatically. Anyway, I appreciate you bearing with me. I'll be on the lookout. Best of luck to you too. – Sum None Jun 16 '22 at 08:11
2

I am currently looking for the best practice for storing cryptocurrency myself and the solution with 'decimal' data type seems to be the most correct one (accuracy depends on the specific cryptocurrency). I decided to leave a comment here only because the answer from @ThickMiddleManager might confuse people, FLOAT/DOUBLE and DECIMAL/NUMERIC are different data types in mysql and are stored differently.

11.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

12.25 Precision Math

Vladimir Gluhov
  • 29
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 29 '22 at 15:12
-1

My answer

I made a small research and calculated, how many digits in a column you need to store top-20 CMC cryptocurrencies in case you don't want to round any numbers. Maximum atomic units count has Shiba Inu: 6 * (10 ** 32).

This means DECIMAL(33, 0) must be enough to store balances of these currencies in the smallest coin units (Satoshi, Wei, etc.). I think I would increase this number to DECIMAL(35, 0) just in case.

Top 20 CMC currencies

However, this won't work for all coins because there may be larger amounts of the smallest units in the total supply. I don't think I will integrate them into my app, though.

About @ThickMiddleManager answer

Besides nagging about the wrong answer there were two points:

  • to use 2 int columns
  • not to use decimals

And the second one is not clear. I've read the answer with attached links twice and didn't get why using decimals is wrong as long as my number fits in the column (i.e. not to put 2.27 into DECIMAL(2, 1)) and I don't perform any math operations in SQL.

Therefore I wish to know, will I have problems using DECIMAL(35, 0)? There is no decimal part, maybe things will go differently? I'm using PostgreSQL, does it make any difference?

trckster
  • 430
  • 1
  • 6
  • 11