How to store bitcoin value and other currencies values together in MySql database?

Question

I need to store bitcoin value and other currencies values together , I have a decimal column (16,8) for amounts. It behaves normally for bitcoin values , but when I want to store other currencies like Usd or Eur It becomes like this eg. 100 Usd becomes 100.00000000 . 0.50 Usd becomes 0.50000000

My question is should I store bitcoin values and other curriencies values in same column? I will have millions of rows, is it bad for performance (read , write , count ) or this is just cosmetic issue?

Answer 1

As far as you use a DECIMAL(16, 8) field, values are stored with 6 decimal digits. It doesn't matter if they are bitcoins, euros, dollars, ...

If you want to use a monetary type, then you should take care and properly configure the server just to hold all values.

8.2. Monetary Types

The money type stores a currency amount with a fixed fractional precision; see Table 8.3. The fractional precision is determined by the database's lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale.

CREATE TABLE Amounts(ID int, Value decimal(16,8), Value2 money);

INSERT INTO Amounts VALUES
(1, 100, 100), (2, 12.22636, 12.22636), (3, 0.1, 0.1);

SELECT * FROM Amounts;

✓

3 rows affected

id |        value |   value2
-: | -----------: | -------:
 1 | 100.00000000 | £100.00
 2 |  12.22636000 |  £12.23
 3 |   0.10000000 |   £0.10

dbfiddle here

Answer 2

No good answer...

DECIMAL(16,8) takes 8 bytes, as do DOUBLE and BIGINT.

8 integer digits (in decimal(16,8)) is sufficient for most monetary transactions, but not sufficient to talk about national GDP when expressed in USD, etc. If that is needed, raise the 16 to make more room, at the expense of more bytes (approx 1 byte per 2 digits).

DOUBLE (no (m,n) please), can handle very large values, but suffers some roundoff issues.

BIGINT needs to be divided by the sub-units (10**8 for Bitcoin, 100 for USD/Euro, 10000 for a few other currencies.)