The answer, I guess, is don't use DECIMAL.
I understand now the need for a fraction (someone pointed out that Euro
calculations must be done in upto 4 decimal places with rounding).
All I can say is that:
(a) you multiply the amounts by 10000 (so as to deal with integers), and
(b) Split each amount into 3 32-bit numbers, or 2 64-bit numbers, and
store each of these in 3 or 2 columns.
(c) Use some high-level data type that abstracts large exact numbers (if
you're using Java, "java.math.BigInteger" or "java.math.BigDecimal"
qualifies, but it's a bit of a pain to construct a BigInteger from an int[]
array).
Alternatively, if you're using Java throughout, you can start with
BigDecimals, and do the following:
* To write into the database:
- extract the scale, and the unscaled BigInteger, from the BigDecimal.
- Convert the BigInteger to a byte array.
- store the bigdecimal as a byte array (TINYBLOB, or a hex string in a
CHAR(N) column) by calling BigInteger.toByteArray();
* To read it back, read in the byte array and scale, and do
new BigDecimal(new BigInteger(bytearray), scale)
-----Original Message-----
From: Gyulay Gabor [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 11:16 PM
To: Shankar Unni
Cc: [EMAIL PROTECTED]
Subject: Re: About huge numbers
On Thu, 15 Nov 2001, Shankar Unni wrote:
Thank you for your answers.
> Generally, you don't want to store currencies in floating point,
anyway,
> and it's unfortunate that MySQL implements DECIMAL as floating point
rather
> than a variable-length BCD (which is exact).
Yes. This is strange, because we would like to convert our
applications from foxpro.
It seems, we have to store these numbers as floating
point, because we have to handle all currencies the same
way.
> However, I tried a sanity check on that number: taking a value of the
order
> of 100 trillion (say, US dollars - this would be of the order of 50x
the
> amount of the current US annual budget), converting it to, say,
Turkish
> Lire (150000x), I get a number that barely exceeds the signed 64-bit
limit,
> and definitely fits in an unsigned 64-bit number.
>
> For Italian lire, the number is a hundred times smaller, and should be
well
> within reach of 64-bit computation.
>
> Are you absolutely *sure* you can't stick to a 64-bit number? Are you
> really dealing with hundreds of quadrillions of US dollars?
We would like to prepare the system to handle extreme values
without errors.
To store the annual money circulation of an huge
Hungarian company, we must use 12+2 digits at least
123.456.789.012,12 HUF
and the system should convert it to ITL, USD etc.
This way it exceeds the system's limit, and we
cannot use integers, because we need to store fractions too.
If we would multiply by 10000, we would get this:
(Right now there is no real demand to count with these
numbers, but what if it occurs in the future...)
mysql> create table try (money decimal(20,2));
mysql> insert into try values (1234567890123456.12);
mysql> select * from try;
+---------------------+
| money |
+---------------------+
| 1234567890123456.00 |
+---------------------+
1 row in set (0.00 sec)
Of course this is the biggest number we plan to handle, and
this is the worst case we would like to convert, and this
could be only a subconscious fear.
Thank you again,
Gyulay Gabor
ps.
I tried this under foxpro, and got the same result.
So the problem existed in our previous system, and we
haven't even know about it.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
