How to store cryptocurrency balance in mysql?

Question

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.

Answer 1

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.

Answer 2

The design of the Ethereum VM has nothing to do with what you need to represent an ETH amount.

1 ETH is represented by 10^18 of its natural unit ( 1 Ether = 1,000,000,000,000,000,000 wei ).

You'll need, at most, DECIMAL(36,18) which is huge enough to accommodate any conceivable amount at the required precision level.

The Ethereum VM only has a 256-bit (32-byte) numerical type for reasons that are never fully articulated, so internally that's what it has to use for amount values.

Answer 3

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